Changing text to numberformat via textbox on a form

[Guest]

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

 

[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

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!

 

[Guest]

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

 

[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

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!