Bishawajit Chakraborty
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.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
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 and 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 name Ok and Cancel in the below image.
Read More: How to Use VBA to Get Value from Userform Textbox in Excel
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
- 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.
Read More: How to Hide Textbox Using Excel VBA (with Quick Steps)
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.
Read More: How to Use VBA Textbox Properties in Excel (with Easy Steps)
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.
Read More: Make an Alternative to Datepicker in Excel (2 Easy Methods)
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, ExcelDemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.
