Inserting Date into Userform Testbox

[raschwab]

I am endeavouring to insert a date into a textbox on a userform, using the following code:

Private Sub tbDate_Change()
    Me.tbDate.Value = Format(Me.tbDate.Value, "dd mmm yy")
End Sub

I initialized the testbox to reflect the current date; however, when I attempt to alter the date, a revised date appears as soon as I enter the first character of the date that I want.

Any help/advice would be most appreciated.

 

[Domenic]

Try using the AfterUpdate event instead...

[font=Verdana][color=darkblue]Private[/color] [color=darkblue]Sub[/color] tbDate_AfterUpdate()
    Me.tbDate.Value = Format(Me.tbDate.Value, "dd mmm yy")
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[raschwab]

Thanks for responding, Domenic. The "AfterUpdate" event does resolve the main problem. Now the format comes in as "4/29/11" as opposed to the "dd mmm yy" format that I would like to see. Any ideas as to how how I could overcome this issue?

Incidentally, I have designed the userform such that the user can call up Calendar Control 12.0. This works perfectly.

 

[raschwab]

I was incorrect when I stated that the output format was "4/29/11". This was the format resulting from the initialization code. That said, when I type "11apr11" the output does not change to "11 Apr 11" per the "dd mmm yy" format specified. Would appreciate any insights as to how I could circumvent this issue.

Rick Schwab

 

[Domenic]

When entering a value that represents a date, it needs to be entered in a format that can be recognized by Excel, such as 4/15, 4/15/11, 4/15/2011, Apr 15, 2011, April 15, 2011, 15 Apr 11, and 15 Apr 2011.

 

[raschwab]

Thanks, Domenic. It does work if I follow the format. I was hoping that it would work the way I had tried it, which I was able to do when I insert the data directly into the spreadsheet cell. Thanks again.

 

[Domenic]

Since you have Calendar Control 12.0, why not use it to allow the user to select the date, instead of having the user enter the date in a textbox?

 

[Norie]

Why restrict the user to the format they enter the date in?

If you need the date entered converted to a particular format for further use it's pretty easy to check they have entered a valid date with the IsDate function.

If they have you can convert it to the format you need, if they haven't you can ask them to re-enter the date.

 

[Domenic]

Why restrict the user to the format they enter the date in?

If you need the date entered converted to a particular format for further use it's pretty easy to check they have entered a valid date with the IsDate function.

If they have you can convert it to the format you need, if they haven't you can ask them to re-enter the date.

Hi Norie,

I had thought about using the IsDate function to validate a date entry. However, it returns True if a date is mistakingly entered in the following format...

"30-Feb-11"

Hence, my suggestion to use the Calendar control. I'd appreciate any comments.

 

[Norie]

Domenic

Forgot about that.

I suppose you could use IsDate as the 1st 'line of defence' and then validate further.

The Calendar control might be the best bet, but it isn't necessarily going to be available to everyone.