Changing text to numberformat via textbox on a form

S

Stef

I use textboxes on a form to enter various data to some cells in a spreadsheet. (Not a table). All values are saved as text regardless of if they are actually numbers. So I tried ths:
....
With Range("MyCell")
.Value = TextBox3
.NumberFormat = "#,##0"
End With
....
The cell is now formatted as a number but I have to doubleclick it to appear as a number as it still looks like text and the green little mark says it has been saved as text!

How do I get around this?

Regards Stef
 
H

Harald Staff

Hi Stef

Formatting as number will not convert text to numbers. Why is it text in the
first place ? Shouldn't be. Anyway, you can force a number-lookalike text to
be number by multiplying it by 1:

Private Sub CommandButton1_Click()
With Range("MyCell")
.Value = TextBox3.Text
.Value = .Value * 1
.NumberFormat = "#,##0"
End With
End Sub

HTH. Best wishes Harald

Stef said:
I use textboxes on a form to enter various data to some cells in a
Click to expand...
spreadsheet. (Not a table). All values are saved as text regardless of if
they are actually numbers. So I tried ths:
...
With Range("MyCell")
.Value = TextBox3
.NumberFormat = "#,##0"
End With
...
The cell is now formatted as a number but I have to doubleclick it to
Click to expand...
appear as a number as it still looks like text and the green little mark
says it has been saved as text!
 
S

Stef

Thanks! your tip fixed my problem. I do not know for sure but using textboxes to enter data from a form seems to save this data as text by default.

Stef
 
T

Tom Ogilvy

Private Sub CommandButton1_Click()
With Range("MyCell")
.Value = cdbl(TextBox3.Text) ' or clng(Textbox3.Text)
.NumberFormat = "#,##0"
End With
End Sub

Everything in a textbox is text.

--
Regards,
Tom Ogilvy

Stef said:
I use textboxes on a form to enter various data to some cells in a
Click to expand...
spreadsheet. (Not a table). All values are saved as text regardless of if
they are actually numbers. So I tried ths:
...
With Range("MyCell")
.Value = TextBox3
.NumberFormat = "#,##0"
End With
...
The cell is now formatted as a number but I have to doubleclick it to
Click to expand...
appear as a number as it still looks like text and the green little mark
says it has been saved as text!
 
Top