How to Make Textbox Datepicker with Excel VBA (with Easy Steps)

Users frequently want to click a button and choose a date. The same holds true for Microsoft Excel programmers. Making use of InputBox and MessageBox while creating VBA macros is the simplest and most popular method of user interaction. This article will show how to make a textbox datepicker with Excel VBA.

Step-by-Step Procedures to Make a Textbox Datepicker with Excel VBA

The Datepicker, a new interface, is introduced in this article. The Datepicker gives users the option of entering a date in the textbox or selecting it from a calendar (to open click the popup button). So, you can follow the steps accordingly to make an Excel VBA textbox date picker.

Step 1: Creating Two UserForms

We’ll take a look at an Excel VBA application that generates a UserForm with CommandButton, Label, TextBox, and ComboBoxes for creating a textbox datepicker in Excel.

Firstly, go to the Insert option.
Then, click on the UserForm option.
Therefore, the Userform that we’re going to build looks like this in the below image.
Besides, we will add Labels, command buttons, and text boxes
Here, we write Date as a label at position 1, generating a textbox and command button at positions 2 and 3 respectively.

Now, we are going to create another UserForm here from the Insert option.
So, we will generate three label names as Day, Month, and Year at position 1 and three combo boxes for every label name on the right side.
Then, we will make two command buttons here named OK and Cancel in the below image.

Step 2: Opening Datepicker Dialog Box

In this section, we will show how to open the datepicker dialog box form in the Excel sheet by generating a VBA Macro.

Therefore, copy the following code and paste it into Module1.

Sub openDPicker()
DatePickerForm.Show
End Sub

Then, save it and press F5 to run the program.

Finally, you will see the date picker dialog box in the below image.

Step 3: Making a Datepicker Calendar

VBA is a programming language that may be used for a variety of tasks, and different types of users can use it for those tasks. Using the Alt + F11 keyboard shortcut, you can launch the VBA editor. In the last section, we will generate VBA code that makes it very easy to encode data in Excel. Here, we will create a calendar by applying VBA Macro.

So, right-click on the UserForm named calpicker to view the code we have created.

Then, paste the following VBA code here and press F5 to run the program.

Private Sub UserForm_Initialize()
Dim p As Integer
With Me.bcmDay
For p = 1 To 31
.AddItem p
Next p
.Value = VBA.Format(VBA.Date, "D")
End With
With Me.bcmMonth
For p = 1 To 20
.AddItem VBA.Format(VBA.DateSerial(2022, p, 1), "MMMM")
Next p
.Value = VBA.Format(VBA.Date, "MMMM")
End With
With Me.bcmYear
For p = VBA.Year(Date) - 30 To VBA.Year(Date) + 30
.AddItem p
Next p
.Value = VBA.Format(VBA.Date, "YYYY")
End With
End Sub
Private Sub CommandButton1_Click()
DatePickerForm.pDate.Value = bcmDay & "-" & bcmMonth & "-" & bcmYear
calPicker.Hide
End Sub
Private Sub CommandButton2_Click()
calPicker.Hide
End Sub

VBA BREAKDOWN

Firstly, we will call our subroutine,

Private Sub UserForm_Initialize()

Secondly, we will declare our variable,

Dim p As Integer

Thirdly, we will apply the For Loop for listing the days 1 to 31,

With Me.bcmDay
For p = 1 To 31
.AddItem p 
Next p 
.Value = VBA.Format(VBA.Date, "D")
End With

Fourthly, we will apply the For loop for listing months,

With Me.bcmMonth
For p = 1 To 20
.AddItem VBA.Format(VBA.DateSerial(2022, p, 1), "MMMM")
Next p 
.Value = VBA.Format(VBA.Date, "MMMM")
End With

Finally, we will apply the For loop for listing years,

With Me.bcmYear
For p = VBA.Year(Date) - 30 To VBA.Year(Date) + 30
.AddItem p
Next p
.Value = VBA.Format(VBA.Date, "YYYY")
End With
End Sub

Assigning the execution formula for the two CommandButtons,

Private Sub CommandButton1_Click()
DatePickerForm.pDate.Value = bcmDay & "-" & bcmMonth & "-" & bcmYear
calPicker.Hide
End Sub
Private Sub CommandButton2_Click()
calPicker.Hide
End Sub

Therefore, you will see the calendar button at position 1.
Then, you will get the calendar dialog box on the right side after clicking on the calendar button at position 1.

Step 4: Picking a Date from Calendar

In this step, you need to choose any date from the calendar after clicking the command button named Show Date Picker in the Excel sheet.

Firstly, click on the below Show Date Picker button.

Then, the below dialog box will open.
After that, you can choose any date from this calendar.
So, we will choose 13 December 2022 from the three options available here in the below image.

Step 5: Showing Final Result with Textbox Datepicker

In this section, you will get the final output based on your choice of date from the calendar. Actually, this works like an automated date picker textbox.

As a result, the image below demonstrates the date we selected from the calendar and works as a perfect datepicker.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.

VBA Datepicker.xlsm

Conclusion

In this article, we’ve covered step-by-step procedures to make a text box datepicker with Excel VBA. We sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

Get FREE Advanced Excel Exercises with Solutions!