How to apply number format to text box control?

Kelvin Stott

Active Member
Joined
Oct 26, 2010
Messages
338
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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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...
 
Upvote 0
This worked for me

Code:
Sub test()
With UserForm1
    .TextBox1.Value = Format(0.2, Range("A1").NumberFormat)
    .Show
End With
End Sub
 
Upvote 0
You could try this.
Code:
TextBox1.Value = Format(TextBox1.Value, MyCellRef.NumberFormat)
 
Upvote 0
...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.
 
Upvote 0
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.
Code:
If InStr(TextBox1.Value, "%") Then
 
     RealValue = Val(TextBox1.Value)/100
End If
 
Upvote 0
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.
 
Upvote 0
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.:)
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top