How to apply number format to text box control?

[Kelvin Stott]

Hi,

I've created a simple user form which calculates and enters a default value into a text box control. It all works fine, but how can I display that value in a specific number/date format, according to the number or date format of a specific cell?

I tried this:

TextBox1.NumberFormat = MyCellRef.NumberFormat

But unlike cell ranges, the text box control does not have a numberformat property, so how can I do this?

Any help would be much appreciated, thanks.

 

[VoG]

Try

TextBox1.Value = MyCellRef.Text

 

[Kelvin Stott]

Thanks, but wouldn't that take the value of the cell as well as it's number format?

The value of the text box control is calculated independently, but should be displayed in the same number format as the cell...

 

[VoG]

This worked for me

Sub test()
With UserForm1
    .TextBox1.Value = Format(0.2, Range("A1").NumberFormat)
    .Show
End With
End Sub

 

[Norie]

You could try this.

TextBox1.Value = Format(TextBox1.Value, MyCellRef.NumberFormat)

 

[Kelvin Stott]

Simply beautiful, thank you!

I forgot about the Format function...

 

[Kelvin Stott]

...although there is a slight problem with this I hadn't foreseen:

How do I then let the user change the value in the same number/date format so that the VBA code will understand it? Currently it doesn't seem to understand dates and percentages, where the user enters non-numeric characters like % and / ...

Effectively I want the text box control to act like a normal cell in this regard.

 

[Norie]

I was wondering about that but assumed that since this textbox control was calculated there'd be no user input.

You'll need to convert the formatted text into something understandable and useful.

With dates you can use DateValue to get a 'real' dates.

For percentages you can use something like this.

If InStr(TextBox1.Value, "%") Then
 
     RealValue = Val(TextBox1.Value)/100
End If

 

[Kelvin Stott]

It's not the most elegant solution, but I think I can get this to work, thanks.

Maybe Microsoft can think about adding a numberformat property to its user form controls in the future.

 

[Norie]

Kelvin

I really can't see it happening.

Anyway, they are textboxes after all.

I sometimes just stick a small label next to the textbox, a single character like a % doesn't take up a lot of room and can save a fair bit of code.