Creating a drop-down calendar (or date picker) in Excel using VBA

Creating a drop-down calendar (or date picker) in Excel using VBA (Visual Basic for Applications) typically involves using a calendar control or a custom dialog box. While Excel does not provide a built-in calendar control in all versions, you can work around this by using a UserForm (a custom form) or other controls.

Step 1: Add a Calendar Control in a UserForm

• Open the VBA Editor:
  • Open Excel and press Alt + F11 to open the VBA editor.
• Create a New UserForm:
  • In the VBA editor, go to the Insert menu and select UserForm to create a new UserForm.
• Add a Calendar Control:
  • In the toolbox (if visible), look for the Microsoft Date and Time Picker Control or Microsoft Calendar Control. If these controls are unavailable (which may vary by Excel version), we will simulate a calendar using buttons or labels.

If the control is not visible, right-click on the toolbox, choose « Additional Controls, » and add the calendar control if available.

• Add a Button to Open the Calendar:
  • You can add a Button on your Excel sheet that will open the UserForm.

Step 2: Create a Button to Show the Calendar

Go back to your Excel sheet, then add a button with the following steps:

• Insert a Button:
  • Go to the Developer tab, then click Insert, and choose a button from the Form Controls.
  • Place the button on your sheet.
• Assign a Macro to the Button:
  • Right-click the button and choose « Assign Macro », then select « New » to create a macro.

Step 3: VBA Code to Show the Calendar

Here is an example VBA code to open a calendar when you click the button. This code uses a UserForm with a DatePicker control and shows the selected date in an Excel cell.

• Code for the UserForm: If you’ve added a DatePicker control to your UserForm, use this code:

' Code for the UserForm
Private Sub Calendar1_Click()
    ' Once a date is selected from the calendar, place it in the active cell
    ActiveCell.Value = Calendar1.Value
    ' Close the UserForm after selection
    Me.Hide
End Sub

• Code to Open the UserForm with the Calendar: This code will open the UserForm when you click the button in Excel.

Sub OpenCalendar()
    ' Show the UserForm containing the calendar
    UserForm1.Show
End Sub

Step 4: Using the UserForm

Now that you’ve created the UserForm and attached the macro to the button:

• Click on the button in Excel.
• The UserForm with the calendar will pop up.
• You select a date, and it will be automatically inserted into the active cell in the Excel sheet.

Option Without DatePicker Control (if not available)

If the DatePicker control is not available in your version of Excel, you can create a custom calendar using buttons and labels to display the days of the month. This is a bit more complex and involves using loops and events to update the calendar each month.

Code VBA for a Custom Calendar (without DatePicker)

Here’s an example of a simple calendar without using a DatePicker control, using buttons to represent the days of the month:

• Create a Calendar Using Buttons and Labels: You can generate a custom calendar using buttons that represent the days of the month. This is a little more involved, but it’s a way to simulate a calendar.

Private Sub UserForm_Initialize()
    ' Initialize the calendar
    Dim i As Integer
    Dim j As Integer
    Dim d As Date
    Dim startDay As Integer
    Dim lastDay As Integer
    Dim currentMonth As Integer
    Dim currentYear As Integer   
    currentMonth = Month(Date)
    currentYear = Year(Date)   
    ' First day of the month
    d = DateSerial(currentYear, currentMonth, 1)
    startDay = Weekday(d, vbSunday)
    ' Last day of the month
    lastDay = Day(DateSerial(currentYear, currentMonth + 1, 1) - 1)   
    ' Clear the old buttons
    For i = 1 To 42
        Me.Controls("Button" & i).Visible = False
    Next i   
    ' Fill buttons with days
    For i = 1 To lastDay
        Me.Controls("Button" & (startDay + i - 1)).Caption = i
        Me.Controls("Button" & (startDay + i - 1)).Visible = True
    Next i
End Sub
Private Sub CommandButton1_Click()
    ' Function to go to the previous month
    currentMonth = currentMonth - 1
    If currentMonth = 0 Then
        currentMonth = 12
        currentYear = currentYear - 1
    End If
    Call UserForm_Initialize
End Sub
Private Sub CommandButton2_Click()
    ' Function to go to the next month
    currentMonth = currentMonth + 1
    If currentMonth = 13 Then
        currentMonth = 1
        currentYear = currentYear + 1
    End If
    Call UserForm_Initialize
End Sub

Explanation:

• UserForm_Initialize: This procedure initializes the calendar, displaying the days of the current month. It uses Weekday to determine the first day of the month and then populates buttons with the days of the month.
• CommandButton1_Click: This moves the calendar to the previous month.
• CommandButton2_Click: This moves the calendar to the next month.

Conclusion

This approach shows you how to create a drop-down calendar in Excel using VBA. You can customize the calendar further based on your needs, for example, by adjusting the layout, adding buttons, or allowing the user to select a date from a dropdown list. If you face limitations with controls in your version of Excel, creating a custom calendar using buttons and labels can be a good alternative