Home » Advanced Excel » How to Use Excel UserForm as Date Picker (with Easy Steps)

How to Use Excel UserForm as Date Picker (with Easy Steps)

Mahfuza Anika Era Mahfuza Anika Era May 16, 2023 0
Get FREE Advanced Excel Exercises with Solutions!

Date Picker is a type of calendar from which you can navigate through the months and years and insert the date into the cell. In an Excel UserForm, a date picker can be created by using VBA. It is very handy to have a Date Picker in Excel. In this article, I have explained how to Create an Excel UserForm Date Picker using VBA. If you want to learn how to create a date picker in only 5 easy steps, follow this article. I will be using the Microsoft Excel 365 version.

Table of Contents hide
Download Practice Workbook
Excel UserForm as Date Picker: Steps You Need to Follow to Create It
Step 01: Insert a UserForm
Step 02: Design the UserForm
Step 03: Change Properties of the Date Picker UserForm
Step 04: Insert VBA Code to the UserForm
4.1 Declare the Variables
4.2 Create a Private Subroutine
4.3 Write VBA Code for UserForm_Initialize Event
4.4 Copy VBA Code for CommandButton_Click Event
4.5 Write VBA Code to ThisWorkbook
Step 05: Assign the Macro to a Button in Excel Sheet
Conclusion

Download Practice Workbook

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

Excel UserForm as Date Picker: Steps You Need to Follow to Create It

I have a dataset of sales data for a grocery shop. You can see, here in the Date & Time of Sale column, that users have to enter the date and time manually. This needs more time and effort, which is not user-friendly. In this case, the date picker will work great. I have demonstrated how to create an Excel UserForm date picker in just five steps. Follow the steps carefully.

Dataset of sales info to create Excel UserForm date picker

Step 01: Insert a UserForm

Firstly, you have to know how to insert a UserForm in Excel. Follow the steps given below:

  • Click on the Developer Tab in Excel.
  • Then, select Visual Basic.

Visual Basic option of Developer Tab in Excel

  • The VBA code Editor window will be opened.
  • Hit the Insert Option.
  • From Insert, select UserFrom.

Inserting UserForm in Excel

  • Finally, you have inserted UserForm in the VBA code Editor.

UserForm in Excel VBA

Step 02: Design the UserForm

In this step, I will add different Controls from the Toolbox to design the UserForm like a date picker. I will use Frame, CommandButton, Label, CheckBox, and ComboBox to design the UserForm. Follow the instructions below:

  • Firstly, add Frame Control from the ToolBox.
  • Adjust the size of the Frame with the UserForm by dragging the corners.

Adding Frame Control in UserForm]\

  • Next, insert ComboBox inside the Frame and resize it so that the Month Names can be fit in it.

Inserting ComboBox in UserForm

  • Now, from the Properties Window which is at the bottom left side of the UserForm, change the Name to “Month_Box”. This name will be used in the code.

Changing Name of ComboBox from Properties

  • Click on the marked 3 dots of the Font option in the Properties Window.

Changing Font of ComboBox from Properties

  • Select Calibri Font, Font style as Bold, and Size as 12.
  • Then, hit the OK button.
  • In this Month_Box, Month Names will be shown.
  • Now, following the same procedure, add another ComboBox beside the previous one.
  • This ComboBox will be used for the Year input.

Inserting ComboBox in UserForm

  • Change the Name of ComboBox to “Year_Box” from the Properties Window.
  • Also change the Font, Font style, and Size just like the previous one.

Changing Name of ComboBox

  • Afterward, add another Frame Control and change the caption to “Calendar”.
  • In this Frame, I will build the calendar with the help of Labels and CommandButtons.

Adding Frame in UserForm

  • Insert one Label Control from the Toolbox inside the Calendar Frame.

Adding Label in UserForm

  • Delete the caption “Label1” in Properties Window and keep it blank.

Changing Caption of the Label Control in UserForm

  • Click on the 3 dots beside the Font option marked in the Picture below.

Editing the Font of Label control from properties

  • Change the Font, Font style, and Size to Calibri, Bold, and 12 respectively.

Changing Font of Label Control

  • Set the BackColor of the Label as Highlight Text.

Changing BackColor of Label

  • At this point, copy the Label 6 times and place these identical Labels side by side.
  • Follow the Image for a better understanding.

Copying Label Control in Excel

  • Change the Caption of the first Label to “Sun” from the Properties Window.

Editing the Caption of Label Control

  • Set the Caption of the other 6 Labels as the Weekday’s short forms.
  • Follow the Screenshot provided below.

Editing the Caption of Label Control

  • Next, add CommandButton Control inside the Calendar Frame.

Inserting CommandButton in UserForm

  • Set the Name Option of the CommandButton as “C1” from the Properties Window.
  • Remove the Caption and keep it Blank. Later, with the help of the VBA code the Caption will be added.

Editing Name and Caption of CommandButton

  • Then, copy the CommandButton 6 more times and paste them serially.
  • It will look like the picture below.

Copying and pasting CommandButtons in UserForm

  • Next, select the 7 CommandButtons at a time and Copy this whole group.

Copying CommandButton Controls in UserForm to create a Date Picker

  • Later, paste this set of CommandButtons for 5 more times.
  • As a result, a calendar-like interface has been created. It has 42 CommandButtons.
  • Change the Name of each CommandButton as C2, C3, C4, …….C42.

Adding CommandButtons to UserForm

  • Insert a CheckBox Control below the Calendar Frame.

Inserting   CheckBox to the UserForm

  • Edit the Caption of the CheckBox as “Add Time”.
  • Change the Font, Font style, and Size to Calibri, Bold, and 12.
  • Press OK.
  • Keep the TextAlign in the Center.

Changing Properties of CheckBox

  • Therefore, the UserForm will look like the image below.

Adding Caption to the CheckBox

  • Insert 4 Label Controls inside Frame1.

Inserting Labels to the UserForm

  • Change the Captions, Font, and Alignment of Text according to the image below.
  • Follow the previous steps to do this.
  • Finally, the design of the UserForm is complete.

Changing Properties of the Labels in UserForm

Step 03: Change Properties of the Date Picker UserForm

In this step, I will change the properties of the UserForm according to my choice. This step is just to make the UserForm look better. You can change the background color, text alignment, caption, font, etc. according to your taste.

  • Here, I have changed the Caption of the Farme1 to “Date Picker”.
  • I have changed the background color of the Calendar Frame.
  • Keep your UserForm simple and easy to read.

Design of a UserForm to create a Date Picker

Step 04: Insert VBA Code to the UserForm

This is the most important step in creating a date picker. In this step, you have to insert VBA codes into the UserForm.

4.1 Declare the Variables

  •  Firstly, declare the variables you will be using in the full process under Option Explicit. These variables will be available in every Sub Procedure.

VBA code to create a date picker

Option Explicit
Dim This_Day As Date
Dim This_Month As Date
Dim This_Year As Date
Dim calender As Boolean
Dim i As Integer

4.2 Create a Private Subroutine 

  • Next, create a Private Sub called Create_Calender.
  • This is the main procedure for creating the calendar for the Date Picker. This Sub will be called in the UserForm Initialize Event procedure.

VBA code to create a Calender

Private Sub Create_Calender()
For i = 1 To 42
    If i < Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value)) Then
        Controls("C" & (i)).Caption = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
        ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d")
        Controls("C" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
        ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d/m/yyyy")
    ElseIf i >= Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value)) Then
        Controls("C" & (i)).Caption = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
        ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d")
        Controls("C" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
        ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d/m/yyyy")
    End If
    If Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
        ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "mmmm") = ((Month_Box.Value)) Then
    If Controls("C" & (i)).BackColor <> &HFFFFFF Then Controls("C" & (i)).BackColor = &HFFFFFF
        Controls("C" & (i)).Font.Bold = True
    If Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
        ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d/m/yyyy") = Format(This_Day, "d/m/yy") Then Controls("C" & (i)).SetFocus
    Else
        If Controls("C" & (i)).BackColor <> &H80000016 Then Controls("C" & (i)).BackColor = &H8000000F
        Controls("C" & (i)).Font.Bold = False
    End If
Next
End Sub

Code Breakdown

For i = 1 To 42

This is a loop that iterates 42 times. The variable “i” is used to keep track of the current iteration.

If i < Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value)) Then

This is a condition that checks whether the current iteration is less than the weekday number of the first day of the selected month and year. This is used to populate the cells that belong to the previous month in the calendar.

Controls("C" & (i)).Caption = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d")
Controls("C" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d/m/yyyy")

This code sets the caption of the current cell to the day of the previous month corresponding to the iteration number, and the “ControlTipText” property of the cell to the full date of that day.

ElseIf i >= Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value)) Then

This is a condition that checks whether the current iteration is greater than or equal to the weekday number of the first day of the selected month and year. This is used to populate the cells that belong to the selected month in the calendar.

Controls("C" & (i)).Caption = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d")
Controls("C" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d/m/yyyy")

This code sets the caption of the current cell to the day of the selected month corresponding to the iteration number, and the “ControlTipText” property of the cell to the full date of that day.

If Format(DateAdd(“d”, (i – Weekday((Month_Box.Value) & “/1/” & (Year_Box.Value))), _

((Month_Box.Value) & "/1/" & (Year_Box.Value))), "mmmm") = ((Month_Box.Value)) Then

This is a condition that checks whether the month of the current day matches the selected month. This is used to set the background color and boldness of the cells that belong to the selected month.

If Controls("C" & (i)).BackColor <> &HFFFFFF Then Controls("C" & (i)).BackColor = &HFFFFFF
Controls("C" & (i)).Font.Bold = True

This code sets the background color of the current cell to white if it is not already white, and sets the font to bold.

If Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
        ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "mmmm") = ((Month_Box.Value)) Then

This line checks if the date value of the current calendar control object matches today.

  If Controls("C" & (i)).BackColor <> &HFFFFFF Then Controls("C" & (i)).BackColor = &HFFFFFF
 Controls("C" & (i)).Font.Bold = True

The If statement checks if the BackColor property of the control with a name that is a concatenation of “C” and the value of the loop variable i is not equal to the color white. If it is not white, then the BackColor property is set to white (&HFFFFFF) and the Font property is set to bold.

    If Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
        ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d/m/yyyy") = Format(This_Day, "d/m/yy") Then Controls("C" & (i)).SetFocus

Here, the If statement checks if the current date (represented by the variable This_Day) is equal to the date being evaluated in the loop. If this is true, then the SetFocus method is called on the control with the name “C” and the value of the loop variable i.

But, if the first If statement is false, then the code within the following Else statement is executed.

Else
If Controls("C" & (i)).BackColor <> &H80000016 Then Controls("C" & (i)).BackColor = &H8000000F
Controls("C" & (i)).Font.Bold = False
 End If
    Next
End Sub

Else statement checks if the BackColor property of the control with the name “C” and the value of the loop variable i is not equal to a specific color (&H80000016). If it is not that color, then the BackColor property is set to a different color (&H8000000F), and the Font property is set to not be bold. The code then moves on to the next value of the loop variable i, and the procedure ends with the End Sub statement.

4.3 Write VBA Code for UserForm_Initialize Event

  • Copy the code under a Private Sub UserForm_Initialize.
  • This code will Run when the UserForm Initialize event occurs.

VBA code of Private Sub UserForm Initialize

Private Sub UserForm_Initialize()
Application.EnableEvents = False
This_Day = Date
This_Month = Format(This_Day, "mm")
This_Year = Format(This_Day, "yyyy")
For i = 1 To 12
    Month_Box.AddItem Format(DateSerial(Year(Date), Month(Date) + i, 0), "mmmm")
Next
Month_Box.ListIndex = Format(Date, "mm") - Format(Date, "mm")
For i = -10 To 30
    If i = 1 Then Year_Box.AddItem Format((This_Day), "yyyy") Else Year_Box.AddItem _
    Format((DateAdd("yyyy", (i - 1), This_Day)), "yyyy")
Next
Year_Box.ListIndex = 11
calender = True
Call Create_Calender
Application.EnableEvents = True
Time_Label.Caption = Time()
Date_Label.Caption = Format(Date)
If calender = True Then
    UserForm1.Caption = Month_Box.Value & " " & Year_Box.Value
End If
End Sub

Code Breakdown

Private Sub UserForm_Initialize()

This line starts the initialization subroutine for a user form. This code will be executed when the user form is initialized.

Application.EnableEvents = False

This line turns off event handling in Excel. This is done to avoid running any other event-driven macros that may be running in the background and potentially cause conflicts.

This_Day = Date
This_Month = Format(This_Day, "mm")

This line sets a variable called “This_Day” to the current date and “This_Month” to the current month in a two-digit format.

This_Year = Format(This_Day, "yyyy")

Next, this line sets a variable called “This_Year” to the current year in a four-digit format.

For i = 1 To 12

This line sets up a loop that will iterate through the months of the year.

Month_Box.AddItem Format(DateSerial(Year(Date), Month(Date) + i, 0), "mmmm")

Here, this line adds each month name to a drop-down list box called “Month_Box”.

Month_Box.ListIndex = Format(Date, "mm") - Format(Date, "mm")

This line sets the default selection in the “Month_Box” drop-down list to the current month.

For i = -10 To 30 

It sets up another loop that will iterate through a range of years.

If i = 1 Then Year_Box.AddItem Format((This_Day), "yyyy") Else Year_Box.AddItem Format((DateAdd("yyyy", (i - 1), This_Day)), "yyyy")

This line adds each year to a drop-down list box called “Year_Box”.

Year_Box.ListIndex = 11

Then, this line sets the default selection in the “Year_Box” drop-down list to the current year minus 10.

calender = True

This line sets a variable called “calender” to “True”.

Call Create_Calender

Here, it calls a subroutine called “Create_Calender”, which creates a calendar in the user form.

Application.EnableEvents = True

This line turns event handling back on in Excel.

Time_Label.Caption = Time() 

Again, this line sets the caption for a label called “Time_Label” to the current time.

Date_Label.Caption = Format(Date)

This line sets the caption for a label called “Date_Label” to the current date.

If calender = True 
            Then UserForm1.Caption = Month_Box.Value & " " & Year_Box.Value

Finally, it sets the caption for the user form to the selected month and year if the “calender” variable is “True”.

  • Click on the Year_Box ComboBox in the UserForm and a Subroutine, Private Sub Year_Box_Change Will be created.
  • Write the following code to this Subroutine.
  • This code will mainly call the Create_Calender Subroutine whenever the Month and Year is changed in the Month_Box and Year_Box ComboBoxes.

VBA code  to create a Date Picker

Private Sub Year_Box_Change()
If Me.Month_Box.Value <> "" And Me.Year_Box.Value <> "" Then
 Call Create_Calender
End If
End Sub

Code Breakdown

Private Sub Year_Box_Change()
If Me.Month_Box.Value <> "" And Me.Year_Box.Value <> "" Then

The code begins with the declaration of a subroutine named “Year_Box_Change” using the keyword “Private Sub”. The subroutine does not return any value and is meant to be used within the current module only.

The next line checks if both the “Month_Box” and “Year_Box” combo boxes have a value. The “Me” keyword refers to the current object, which is the form containing the combo boxes.

Call Create_Calender
End If
End Sub

Overall, this part of the code is checking if two specific combo boxes have a value, and if they do, it calls another subroutine called Create_Calender.

4.4 Copy VBA Code for CommandButton_Click Event

  • Click on the CommandButton Named “C1” and a Subroutine, Private Sub C1_Click is created.
  • Copy the code given below under this Subroutine.
  • This code will Insert the value of the CommandButton into the Selected cell of the Excel Sheet.

VBA code of private Sub CommandButton click

Private Sub C1_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C1.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C1.ControlTipText
End If
End Sub

Code Breakdown

Private Sub C1_Click()

The code is triggered by a click event on a control object named “C1”.

If Time_Box.Value = True Then
   Selection.Value = Me.C1.ControlTipText & ":" & Time()

The code starts with an If statement that checks whether a checkbox object named “Time_Box” is checked (i.e., its Value property is True). If the checkbox is checked, the code sets the Value property of the currently selected cell (i.e., the cell that has focus) to a concatenated string that includes the text of the ControlTipText property of the control object “C1” and the current time. The ControlTipText property is a string that appears as a tooltip when the user hovers the mouse over the control object. The Time() function returns the current system time.

ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C1.ControlTipText
End If
End Sub

If the checkbox is not checked (i.e., its Value property is False), the code sets the Value property of the currently selected cell to the text of the ControlTipText property of the control object “C1” only.

In summary, this code allows the user to insert a text value with or without the current timestamp into the currently selected cell, depending on whether a checkbox is checked or not, by clicking on a control object named “C1”.

  • Click on each CommandButton of the Calender and copy the same code into the subroutines.
  • You have to just change the CommandButton Name accordingly into the code.
  • I have provided the image of the code so that you can understand it clearly.

VBA code of creating a excel userform date picker

Private Sub C1_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C1.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C1.ControlTipText
End If
End Sub
Private Sub C11_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C11.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C11.ControlTipText
End If
End Sub
Private Sub C12_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C12.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C12.ControlTipText
End If
End Sub
Private Sub C13_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C13.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C13.ControlTipText
End If
End Sub
Private Sub C14_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C14.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C14.ControlTipText
End If
End Sub
Private Sub C15_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C15.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C15.ControlTipText
End If
End Sub
Private Sub C16_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C16.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C16.ControlTipText
End If
End Sub
Private Sub C17_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C17.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C17.ControlTipText
End If
End Sub
Private Sub C18_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C18.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C18.ControlTipText
End If
End Sub
Private Sub C19_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C19.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C19.ControlTipText
End If
End Sub
Private Sub C2_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C2.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C2.ControlTipText
End If
End Sub
Private Sub C20_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C20.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C20.ControlTipText
End If
End Sub
Private Sub C21_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C21.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C21.ControlTipText
End If
End Sub
Private Sub C22_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C22.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C22.ControlTipText
End If
End Sub
Private Sub C23_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C23.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C23.ControlTipText
End If
End Sub
Private Sub C24_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C24.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C24.ControlTipText
End If
End Sub
Private Sub C25_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C25.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C25.ControlTipText
End If
End Sub
Private Sub C26_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C26.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C26.ControlTipText
End If
End Sub
Private Sub C27_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C27.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C27.ControlTipText
End If
End Sub
Private Sub C28_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C28.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C28.ControlTipText
End If
End Sub
Private Sub C29_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C29.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C29.ControlTipText
End If
End Sub
Private Sub C3_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C3.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False The
    Selection.Value = Me.C3.ControlTipText
End If
End Sub
Private Sub C30_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C30.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C30.ControlTipText
End If
End Sub
Private Sub C31_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C31.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C31.ControlTipText
End If
End Sub
Private Sub C32_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C32.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C32.ControlTipText
End If
End Sub
Private Sub C33_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C33.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C33.ControlTipText
End If
End Sub
Private Sub C34_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C34.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C34.ControlTipText
End If
End Sub
Private Sub C35_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C35.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C35.ControlTipText
End If
End Sub
Private Sub C36_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C36.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C36.ControlTipText
End If
End Sub
Private Sub C37_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C37.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C37.ControlTipText
End If
End Sub
Private Sub C38_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C38.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C38.ControlTipText
End If
End Sub
Private Sub C39_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C39.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C39.ControlTipText
End If
End Sub
Private Sub C4_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C4.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C4.ControlTipText
End If
End Sub
Private Sub C40_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C40.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C40.ControlTipText
End If
End Sub
Private Sub C41_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C41.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C41.ControlTipText
End If
End Sub
Private Sub C42_Click()
If Time_Box.Value = True Then
    Selection.Value = Me.C42.ControlTipText & ":" & Time()
ElseIf Time_Box.Value = False Then
    Selection.Value = Me.C42.ControlTipText
End If
End Sub
  • Click on ThisWorkBook below the VBAProject marked in the image.

4.5 Write VBA Code to ThisWorkbook 

Writing VBA code to ThisWorkbook

  • Write the code in ThisWorkBook so that the UserForm will be shown.

VBA code to show the UserForm

Sub UserForm_Show()
UserForm1.Show
End Sub

Step 05: Assign the Macro to a Button in Excel Sheet

To use this date picker more efficiently, it will be great to create a button for this macro. In this step, you will learn how to assign the Macro to a button.

  • First, Select the Developer Tab.
  • Then, click on Insert.
  • Select the first button from the Form Controls.

Inserting a button to the excel sheet

  • A Button has been created on the Excel Sheet.
  • Edit the caption of the Button as “Date Picker”.
  • Keep the Button near the dataset where you need to insert a Date.

Editing the text of a control button in excel

  • Right-click on the Date Picker Button.
  • Select Assign_Macro… option.

Assigning a macro to the button

  • Click on the arrow beside the Button4_Click.
  • The macro of the UserForm will be shown.

Assign a macro to the control button

  • Select ThisWorkbook.UserForm_Show.
  • Press OK.
  • My created macro is assigned to this Button.

Assign the created macro of the date picker to the button

  • Play the video to see the Final Output.

Conclusion

I have demonstrated a step-by-step procedure for creating an Excel UserForm as Date Picker by using VBA. I hope that if you follow the steps carefully, you can create your own Date Picker in Excel UserFrom. Moreover, the UserForm I have created will give you the time as well. So, now you can design your UserForm according to your preferences. If you have any questions, please leave a comment. I will try to help.

Tags:

Mahfuza Anika Era

Mahfuza Anika Era

Hello! Welcome to my Profile. Recently I have been graduated from Bangladesh University of Engineering and Technology in Civil Engineering. Being a fresh graduate, I want to build up my skill in article writing about Microsoft Excel and VBA. I am also interested in research and development. I believe in learning something new every day and implementing my knowledge more effectively.

Related Articles
We will be happy to hear your thoughts

Leave a reply

About ExcelDemy.com

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact | Privacy Policy | TOS

Affiliate Disclosure:

ExcelDemy.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program. I earn a small commission if you buy any products using my affiliate links to Amazon.

DMCA.com Protection Status

Join ExcelDemy Forum

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo