excel - Formatting MM/DD/YYYY dates in textbox in VBA

Question

I'm looking for a way to automatically format the date in a VBA text box to a MM/DD/YYYY format, and I want it to format as the user is typing it in. For instance, once the user types in the second number, the program will automatically type in a "/". Now, I got this working (as well as the second dash) with the following code:

Private Sub txtBoxBDayHim_Change()
    If txtBoxBDayHim.TextLength = 2 or txtBoxBDayHim.TextLength = 5 then
    txtBoxBDayHim.Text = txtBoxBDayHim.Text + "/"
End Sub

Now, this works great when typing. However, when trying to delete, it still enters in the dashes, so its impossible for the user to delete past one of the dashes (deleting a dash results in a length of 2 or 5, and the sub is then run again, adding in another dash). Any suggestions on a better way to do this?

Answer 1

I never suggest using Textboxes or Inputboxes to accept dates. So many things can go wrong. I cannot even suggest using the Calendar Control or the Date Picker as for that you need to register the mscal.ocx or mscomct2.ocx and that is very painful as they are not freely distributable files.

Here is what I recommend. You can use this custom made calendar to accept dates from the user

PROS:

1. You don't have to worry about user inputting wrong info
2. You don't have to worry user pasting in the textbox
3. You don't have to worry about writing any major code
4. Attractive GUI
5. Can be easily incorporated in your application
6. Doesn't use any controls for which you need to reference any libraries like mscal.ocx or mscomct2.ocx

CONS:

Ummm...Ummm... Can't think of any...

HOW TO USE IT (File missing from my dropbox. Please refer to the bottom of the post for an upgraded version of the calendar)

1. Download the Userform1.frm and Userform1.frx from here.
2. In your VBA, simply import Userform1.frm as shown in the image below.

Importing the form

RUNNING IT

You can call it in any procedure. For example

Sub Sample()
    UserForm1.Show
End Sub

SCREEN SHOTS IN ACTION

NOTE: You may also want to see Taking Calendar to new level