Excel

Calendar Control to Select Dates on a Userform

Ease of Use

Intermediate

Version tested with

2002 

Submitted by:

Jacob Hilderbrand

Description:

The calendar control allows the user to select a date from a real calendar view. 

Discussion:

Instead of typing in a date manually you can have a calendar pop up for a user to select the date, then input that date into a cell. This ensures that the value put into the cell is a real date, and also provides a visual so that the user knows on which day of the week the date falls. 

Code:

instructions for use

			

'This code is for the Worksheet Module ONLY 'In the Sheet Code Module for the sheet you want this to work on paste: Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim Rng1 As Range 'Assign the range to work with Set Rng1 = Range("A1:B10") 'Only work on assigned range If Intersect(Target, Rng1) Is Nothing Then Exit Sub 'Cancel cell editing that would normally trigger when you double click Cancel = True 'Call the userform UserForm1.Show End Sub 'End worksheet module code 'This code is for the Userform Module ONLY 'In the Userform Code Module paste: Option Explicit Private Sub CommandButton1_Click() 'OK 'Place the date from the calendar into the active cell ActiveCell.Value = UserForm1.Calendar1.Value 'Dismiss the userform Unload Me End Sub Private Sub CommandButton2_Click() 'Cancel 'Dismiss the userform Unload Me End Sub Private Sub UserForm_Initialize() 'Change the Calendar date to todays date UserForm1.Calendar1.Value = Date End Sub 'End userform module code

How to use:

  1. Open Excel.
  2. Press Alt + F11 (That is Press and hold Alt, then Press F11) to open the Visual Basic Editor.
  3. From the Insert Menu select "Insert Userform".
  4. From the Control Toolbox, add two CommandButtons to the userform. Size and position them as desired.
  5. From the Control Toolbox, add a Calendar Control (Note: If the Calendar Control is not on the Control Toolbox then: Right click on the Control Toolbox and select "Additional Controls...", Browse for "Calendar Control 10.0".
  6. Size and position the Calendar Control as desired.
  7. Double click on the userform to go to the code module.
  8. Delete all code that is there and paste in the code from the above section labeled "In the Userform Code Section paste:"
  9. On the left hand side of the window in the project explorer find where it lists the sheets in the workbook and double click on the sheet that you want this code to run on (i.e. if you want this to run on Sheet1 then double click on that sheet to view its code module).
  10. Paste the code from the above section labeled 'In the Sheet Code Module for the sheet you want this to work on paste: "
  11. Close VBE (Click the X in the top right hand corner).
 

Test the code:

  1. Double click within the range specifed in the code on the proper sheet and the userform should pop up with the calendar to allow you to select the date to input. (In our sample, this range is colored yellow for your convenience.)
 

Sample File:

Calendar Control.zip 10.79KB 

Approved by mdmackillop


This entry has been viewed 556 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express