|
|
|
|
|
|
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
|
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Rng1 As Range
Set Rng1 = Range("A1:B10")
If Intersect(Target, Rng1) Is Nothing Then Exit Sub
Cancel = True
UserForm1.Show
End Sub
Option Explicit
Private Sub CommandButton1_Click()
ActiveCell.Value = UserForm1.Calendar1.Value
Unload Me
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
UserForm1.Calendar1.Value = Date
End Sub
|
How to use:
|
- Open Excel.
- Press Alt + F11 (That is Press and hold Alt, then Press F11) to open the Visual Basic Editor.
- From the Insert Menu select "Insert Userform".
- From the Control Toolbox, add two CommandButtons to the userform. Size and position them as desired.
- 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".
- Size and position the Calendar Control as desired.
- Double click on the userform to go to the code module.
- Delete all code that is there and paste in the code from the above section labeled "In the Userform Code Section paste:"
- 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).
- Paste the code from the above section labeled 'In the Sheet Code Module for the sheet you want this to work on paste: "
- Close VBE (Click the X in the top right hand corner).
|
Test the code:
|
- 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.
|
|