TextBox number / date / time formatting?

How do you format a textbox in a vba form to display numbers, dates or times?

I’ve formatted the cells that hold the txtboxes’ data but some still display incorrectly.

Some of the textboxes that I want formatted as dates display in a number format…
Some that I want formatted as time, give me the equivalent decimal number…
Some that I want as numbers display as dates…

What make this so pathetic is that I have searched for a while (days) for this answer and still coming up blank!

Your help is appreciated thanks! Phil

Reading between the lines I have assumed you are populating the Textbox from someother source than a person entering the data in the Textbox.

If this is the case then you need to use the Format command as part of the command that places the data into the textbox

Please Login or Register  to view this content.

Thanks for the response mudraker,

Please be patient with this wordy explanation.

I'm trying to write a simple (?) workorder database program. For the most part it works well with a few bugs. This is one of the bugs.


I've got a test box on a vba form that I want the user to enter the time of a call.

The time is stored in cell c1 via the textbox properties 'control source' (for later storage in the db sheet).

If I type '8:30 pm' it goes in as a text format in the cell c1 and the textbox.

That works ok as a text format (but i'd rather have a time format).

BUT, if I type '8:30' in the textbox, cell c1 changes to a time format but the textbox shows a decimal value. (I understand that time=decimal)

From then on even if i type '8:30 pm' it shows the decimal value in the textbox.


It's like the textbox and the cell c1 will stay in a text format condition with the 'hh:mm pm' typed into the textbox, BUT if I type 'HH:mm' the format of cell c1 changes to time and the textbox goes to a decimal.

Other textboxes do similarly, i.e. I have a textbox for a serial number that will change to a 'date'...

So, the question is, how do you format textboxes for dates, times or numbers?

Thanks alot for help, Phil

To put it bluntly

You are dealing with a Textbox - Textboxes are dumb.
To them everything is Text

You can not set a property on a textbox to have it display ar treat the entry as a particular type - See More Comments at the end of this posting

You need to convert it to the format you require

To have a date that is entered in a text box entered into a1 as a date try

Please Login or Register  to view this content.

Other conversion commands:-

CBool(expression)

CByte(expression)

CCur(expression)

CDate(expression)

CDbl(expression)

CDec(expression)

CInt(expression)

CLng(expression)

CSng(expression)

CStr(expression)

CVar(expression)


You can link a Textbox to a worksheet cell & it will display the current entry in that cell in a format that suits the cell entry - May not be in the exact format that you want to display it as

Try setting the ControlSource property for the Textbox to =C1

Note:- any change to the Textbox entry will automatically change the entry in C1

None of that worked, nor any of a million variations,
THANKS for your time!

BUT, what did work was I formated the cells as a text and now when I put in 9:45 in the textbox, I get 9:45 in the textbox and in the cells...
Just what I wanted, (I don't give a hoot about 'am/pm'...)

The only way I keep my sanity is knowing, I'm not the first to spend days (really) trying to figure something out that had a simple answer.

Again, THANKS for your time!
I'm sorry I wasted it!

Now on to the next 'little' butt kicker...