Userform Textbox Number format

[Kakiebos]

Hi All.

New to this forum.

I have a userform where the user must fill in the odo meter reading of his trip. Start reading and end reading. This is then sent to the spreadsheet where these readings are used to calculate the trip distance.
I always only used the spreadsheet and filled it in manually. I only got introduced to UserForms recently. This, for me is the next best thing since sliced bread. My formula used to work pretty well, but since I started to use the userform, the value from the TextBox is entered as text and not a number. This causes the formula not to calculate.

Below is a extract of the code that I'm using.


Range("P26").Value = Format(Me.cbESN, "# ##0")


Please help me.

[Kenneth Hobs]

With Range("P26")
.Value = Val(cbESN)
.NumberFormat= "# ##0"
End With

[Paul_Hossler]

Slight tweak to Ken's

I'd use


If IsNumeric (cbESN) then .Value = Cdbl(cbESN)



If you use Val, then it stops at the first non-digit. So "1,234" returns just 1 and "123a4" returns just 123

You can never trust users to follow instructions :devil2:

[Kakiebos]

Thanks.
Will try and give feedback.

[Kakiebos]

With Range("P26")
.Value = Val(cbESN)
.NumberFormat= "# ##0"
End With

Thank you. It worked like a charm on the one spreadsheet and the UserForm that I have there. I now tried a similar format on a different spreadsheet and it is not working. I keep getting a error message: Unable to set the NumberFormat property for the Range class

I actually wanted a different NumberFormat, but tried the exact same one that was working on the first sheet. I really don't know what I'm doing wrong.


With Range("D28")
.Value = Val(Me.tbNt)
.NumberFormat = "# ##0" ' Format I actually want "0.0_ " The format that I used on the other UserForm "# ##0_ " and it worked.
End With

[Kakiebos]

Slight tweak to Ken's

I'd use


If IsNumeric (cbESN) then .Value = Cdbl(cbESN)

Thanks for the reply. Can you please elaborate on where I must add this line of code?



If you use Val, then it stops at the first non-digit. So "1,234" returns just 1 and "123a4" returns just 123

You can never trust users to follow instructions :devil2:

[Paul_Hossler]

1. This line


With Range("P26")
If IsNumeric (cbESN) then .Value = Cdbl(cbESN)
.NumberFormat= "# ##0"End With


2. What's in Me.tbNt?

[Kakiebos]

1. This line


With Range("P26")
If IsNumeric (cbESN) then .Value = Cdbl(cbESN)
.NumberFormat= "# ##0"End With


2. What's in Me.tbNt?

Me.tbNt is a textbox in a Timesheet. The number format for the hours worked is to be 0.00
This is for another UserForm

[Paul_Hossler]

I meant what is the value in the Textbox?

[Kenneth Hobs]

Try doing it manually. The format string done manually should be what you use in code.

If your sheet and cell was protected, the value setting should have caused an error.

I don't know where your code resides so I don't know if it resides in the userform or not. Me is not really needed in userform code. It does not hurt anything though if it resides in the userform code.

[Kakiebos]

The value can be anything between 0 and 8 with 0.50 intervals. The code is in the UserForm in a CommandButton_click.

I use the me for intelisence. Because I'm still new to vba, I need all the help from the program to assist me with the correct spelling and naming of code.

I hope you understand what I mean.

[Paul_Hossler]

Again .. when you got the error, what was the SPECIFIC value in tbNt

[Kakiebos]

Oh. Sorry, I didn't understand your question then. It was 1.

[Kenneth Hobs]

Make a simple file and post it if you need further help.

Don't forget to show a manually formatted cell to show the goal.