Formatting MM/DD/YYYY dates in textbox in VBA

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.ShowEnd Sub

SCREEN SHOTS IN ACTION

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

This is the same concept as Siddharth Rout's answer. But I wanted a date picker which could be fully customized so that the look and feel could be tailored to whatever project it's being used in.

You can click this link to download the custom date picker I came up with. Below are some screenshots of the form in action.

To use the date picker, simply import the CalendarForm.frm file into your VBA project. Each of the calendars above can be obtained with one single function call. The result just depends on the arguments you use (all of which are optional), so you can customize it as much or as little as you want.

For example, the most basic calendar on the left can be obtained by the following line of code:

MyDateVariable = CalendarForm.GetDate

That's all there is to it. From there, you just include whichever arguments you want to get the calendar you want. The function call below will generate the green calendar on the right:

MyDateVariable = CalendarForm.GetDate( _    SelectedDate:=Date, _    DateFontSize:=11, _    TodayButton:=True, _    BackgroundColor:=RGB(242, 248, 238), _    HeaderColor:=RGB(84, 130, 53), _    HeaderFontColor:=RGB(255, 255, 255), _    SubHeaderColor:=RGB(226, 239, 218), _    SubHeaderFontColor:=RGB(55, 86, 35), _    DateColor:=RGB(242, 248, 238), _    DateFontColor:=RGB(55, 86, 35), _    SaturdayFontColor:=RGB(55, 86, 35), _    SundayFontColor:=RGB(55, 86, 35), _    TrailingMonthFontColor:=RGB(106, 163, 67), _    DateHoverColor:=RGB(198, 224, 180), _    DateSelectedColor:=RGB(169, 208, 142), _    TodayFontColor:=RGB(255, 0, 0), _    DateSpecialEffect:=fmSpecialEffectRaised)

Here is a small taste of some of the features it includes. All options are fully documented in the userform module itself:

• Ease of use. The userform is completely self-contained, and can be imported into any VBA project and used without much, if any additional coding.
• Simple, attractive design.
• Fully customizable functionality, size, and color scheme
• Limit user selection to a specific date range
• Choose any day for the first day of the week
• Include week numbers, and support for ISO standard
• Clicking the month or year label in the header reveals selectable comboboxes
• Dates change color when you mouse over them

Add something to track the length and allow you to do "checks" on whether the user is adding or subtracting text. This is currently untested but something similar to this should work (especially if you have a userform).

'add this to your userform or make it a static variable if it is not part of a userformprivate oldLength as integerPrivate Sub txtBoxBDayHim_Change()    if ( oldlength > txboxbdayhim.textlength ) then        oldlength =txtBoxBDayHim.textlength        exit sub    end if    If txtBoxBDayHim.TextLength = 2 or txtBoxBDayHim.TextLength = 5 then    txtBoxBDayHim.Text = txtBoxBDayHim.Text + "/"    end if    oldlength =txtBoxBDayHim.textlengthEnd Sub