Date Picker in Excel Sheet Column

You can display a calendar control on a user form if you wish. This action may be triggered by right-clicking a cell (my preference) or by selecting it (your request). The code below is activated by selecting a cell in the range A1:A10.




First, you probably need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.





1) ALT + F11 to open the VBA Editor

2) Insert...User Form to create a blank user form

3) Open the Tools...Additional Controls menu item, then scroll down until you find the Microsoft Date and Timer Picker. Make sure to check its box.

4) Back in the user form, click the new DT Picker control on the Forms toolbar, then draw the control on the form

5) Right-click the user form, then choose "View code". Insert the following subs (which assume that your form is called UserForm1 and it contains a calendar control named DTPicker1.



Private Sub DTPicker1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

ActiveCell.Value = UserForm1.DTPicker1.Value

End Sub



Private Sub UserForm_Initialize()

UserForm1.DTPicker1.Value = Date

End Sub



6) ALT + F11 to return to the Excel Worksheet, then right-click the sheet tab and choose "View code" from the resulting pop-up. Paste the following code there:



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, [A1:A10]) Is Nothing Then Exit Sub



UserForm1.Show

End Sub



7) ALT + F11 to return to the worksheet