Data in cells is "text" instead of "number"

[morzapo]

Hello!

I'm beginning to lose my mind on this problem.

I made a reasonably complex form, where user (me) must insert a lot of data in (mainly) TextBoxes and ComboBoxes:


I wont' bother with translation, but some of you will maybe recognize that I'm trying to calculate heat loses of a room.

Anyways - most of the data is inserted in TextBoxes as a normal number - for example: 0,756 [W/m^2K] (we use comma for decimal delimiter).

All the data is then saved in a worksheet with press on a button. Code behind it is as seen bellow (just an example):

Worksheets("Saved_data").Cells(row_index, 5).Value = Upper_wall_thickness
Worksheets("Saved_data").Cells(row_index, 6).Value = Right_wall_thickness
Worksheets("Saved_data").Cells(row_index, 7).Value = Bottom_wall_thickness
Worksheets("Saved_data").Cells(row_index, 8).Value = Left_wall_thickness

BUT(!) after I check the worksheet "Saved_data", I can see an error in all cells that was taken from TextBoxes. Excel wrote all this data as text and if I place mouse cursor over small sign with exclamation mark I can see error message: "The number in this cell is formatted as text or preceded by an apostrophe."

Of course I can use an option "Convert to number", but there can be a lot of cells with this error and they are placed all over the "Saved_data" worksheet. It is not only time consuming, but it also prevents to do some additional operations I intend to do on "Saved_data" worksheet.

I tried EVERYTHING:

• I placed various format rows prior (and after) to each code row shown above (for example):

Worksheets("Saved_data").Cells(row_index, 5).NumberFormat = "#,##0,00"
Worksheets("Saved_data").Cells(row_index, 5).Value = Upper_wall_thickness
Worksheets("Saved_data").Cells(row_index, 6).NumberFormat = "#,##0,00"
Worksheets("Saved_data").Cells(row_index, 6).Value = Right_wall_thickness
Worksheets("Saved_data").Cells(row_index, 7).NumberFormat = "#,##0,00"
Worksheets("Saved_data").Cells(row_index, 7).Value = Bottom_wall_thickness
Worksheets("Saved_data").Cells(row_index, 8).NumberFormat = "#,##0,00"
Worksheets("Saved_data").Cells(row_index, 8).Value = Left_wall_thickness

• I declared each variable a double:

Dim Upper_wall_thickness As Double
Dim Right_wall_thickness As Double
Dim Bottom_wall_thickness As Double
Dim Left_wall_thickness As Double

• I used some VBA code I found on Internet to solve this problems:

Private Sub CommandButton1_Click()

For Each xCell In Selection
   xCell.Value = CDec(xCell.Value)
Next xCell

End Sub

• etc.

... but NOTHING worked! Can anyone help me with this?

 

[CWatts]

Try this below. By performing arithmatic on a value, it forces it to numerical.

Worksheets("Saved_data").Cells(row_index, 5).Value = Upper_wall_thickness + 0
Worksheets("Saved_data").Cells(row_index, 6).Value = Right_wall_thickness + 0
Worksheets("Saved_data").Cells(row_index, 7).Value = Bottom_wall_thickness + 0
Worksheets("Saved_data").Cells(row_index, 8).Value = Left_wall_thickness + 0

 

[morzapo]

Try this below. By performing arithmatic on a value, it forces it to numerical.
...

It looks like it works, but I get Run-time error '13' (Type mismatch) on some cells.
Although I'm not a programmer at all I think I know what is a problem. At form initialization all values of TextBoxes must be set to "0" (zero) not "" (blank) as I have it now.

I'll report progress tomorrow. Now I must catch some sleep, since it is midnight here in central Europe.

 

[xenou]

When you capture the textbox value you can also trap this error:

Dim x As Double
x = IIF(Textbox1.Value = "", 0, TextBox1.Value)

This still won't work if someone puts actual text and not a number in the textbox - if you are the user, then that won't happen. If others are users you can use more rigorous validations.

 

[morzapo]

Try this below. By performing arithmatic on a value, it forces it to numerical.
...

CWatts, thank you 1000-times!!! It works!

When you capture the textbox value you can also trap this error:

Dim x As Double
x = IIF(Textbox1.Value = "", 0, TextBox1.Value)

This still won't work if someone puts actual text and not a number in the textbox - if you are the user, then that won't happen. If others are users you can use more rigorous validations.

Yes the user will only be me, but during programming I was asking myself constantly how to make as you said "rigorous validations". Can you give me a hint how to assure that I cannot somehow insert "1O" (with letter "o") instead of "10" (number "ten")?
There are a lot of TextBox-es and I cannot imagine to make 60+ MsgBox-es with warnings that I inserted wrong character.