User Form value passing problem

[brusk2]

Hopefully this is a simple fix, but for some reason I can't figure it
out. I have a user from in which the user enters information through
text boxes. One text box has characters and numbers, the rest have
numbers. The one with mixed characters is a date. Specifically a
month and year. The user is instructed to use an abbreviated form,
i.e. June '06 or Sept. '06. The problem is that if the user inputs
something like June 06 instead of June '06 (missing the apostrophe) or
Sept 06 instead of Sept. 06 (no period), when I pass the value to a
cell on a worksheet, I get a number. For example, July 06 = 38904 when
passed using the code:

Range("C27").Value2 = Me.txtMonth.Value

I'd prefer to fix this without using data validation or anything like
that. Thanks in advance for any advice!

B

 

[Harald Staff]

If you get a number then everything works fine. Just format the cell in date
format. Dates are really just numbers, counting number of days since new
year to 1900 (or 1904). The real problem is when you /don't/ get a number,
then it's just a date looking text and not a real date.

Well maybe not. Maybe you don't want dates, just simple text. If so,
preformnat the cell as text instead of date.

HTH. Best wishes Harald

 

[SpartMan1]

Perfect, thanks for the fix and the info!

If you get a number then everything works fine. Just format the cell in date
format. Dates are really just numbers, counting number of days since new
year to 1900 (or 1904). The real problem is when you /don't/ get a number,
then it's just a date looking text and not a real date.

Well maybe not. Maybe you don't want dates, just simple text. If so,
preformnat the cell as text instead of date.

HTH. Best wishes Harald

 

[saiyijinprince]

Nothing is wrong with you user form, Excel is just converting your date to Excel date-time code.
If you try "=datevalue("Jul 06") you will the same number 38904. Unfortunately I don't know any function that converts the number back to date format but the user form is getting the right value.