Values vs text in Excel

[Mollethewizard]

I’ve got a user form with several text boxes in it that the user puts Swedish currency values in like this – 123,55 – as you probably know we use commas as decimal sign in Sweden.

The values are passed to a sheet and then if something is wrong and the user wishes to alter the “input” the values are read back to the form.

With frmBokOm
Range("E10").Value = Val(.txtDeb1.Value)
Range("G10").Value = Val(.txtKred1.Value)
End with

With frmBokOm
.txtDeb1.Value = Range("E10").Value
.txtKred1.Value) = Range("G10").Value
End with


The code for this I have accomplished. But somevere along the road if the user writes and reads several times Excel intrerprets the values as text.

Is there a way to write the code so Excel always interprets all inputs as values?

Mollethewizard

 

[logius]

You could try looking into the Range.NumberFormat property. Maybe setting NumberFormat = "0.00" would give you the result you're looking for. Not sure if "0,00" would work the same.

----------------------------------------
If you are reading this, then you have read too far...

 

[GVF]

Somewhere, your operator has entered a typo.
If an operator can enter something incorrectly, they eventually will.

You must check the data that the operator enters into E10 and G10 to insure that they are numeric values to start with. You must also check the data that is coming out of an editable textbox (bulletproof your code).
Userform textboxes always contain text. When you dump a number into one, the number becomes text (that's why you use the Val function to change it back to a number when you retrieve the value). If you use a variable to pull the value from a textbox or cell, you can trap an error if the value is not correct for the type of variable you are using.

Dim myNum as Long (or integer or other number type)
'If cell E10 contains the number 27.
Userform1.textbox1.value = range("E10").value
'Userform1.textbox1.value = "27"
myNum = userform1.textbox1.value 'myNum will = 27

'If cell E10 contains 2r
Userform1.Textbox1.value = range("E10").value
'Userform1.textbox1.value = "2r"
myNum = Userform1.textbox1.value 'will generate an error

So, if you first set a variable (that can only be a number) to the value of the cell and the cell contains text, you will generate an error you can trap.

err.clear
myNum = Range("E10").Value
if err <> 0 then
msgbox "Wrong"
goto ErrorHandler
else
Userform1.textbox1.value = myNum
end if

Or you can use:
If IsNumeric(Range("E10").Value) then
Userform1.textbox1.value = range("E10").value
Else
MsgBox "Wrong"
Goto ErrorFound
End If