Calender Pop-Up window

[Josh Johansen]

Is there any way to generate a calender pop-up window in order to select a
starting date? Right now I am having the user enter the date manually so
they could possibly make a mistake and choose the wrong start date, a
calender would ensure they pick the right date, thanks.

 

[Wisger]

There is a download you can purchase that will do this:
http://office.microsoft.com/en-us/marketplace/EM102141281033.aspx

 

[Dave Peterson]

Ron de Bruin has a link to a calendar control here:
http://www.rondebruin.nl/calendar.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Rick Rothstein \(MVP - VB\)]

Is there any way to generate a calender pop-up window in order to select a

starting date? Right now I am having the user enter the date manually so
they could possibly make a mistake and choose the wrong start date, a
calender would ensure they pick the right date, thanks.

Here is one way you might consider. Click Tools/Macro/Macros from the Excel
menu bar. Type in a name for this macro (I used PopUpCalendar) and click the
Create button. Once inside the VBA macro editor, click Insert/UserForm from
the VBA menu bar. The UserForm should show along with a Toolbox dialog box
(if not, click View/Toolbox from the menu bar). Right click a blank area of
the Toolbox and select Additional Controls from the popup menu that appears.
On the list that appears, put a check mark next to Microsoft MonthView
Control 6.0 (I sure hope that is a generally available control and not there
because my compiled version of VB6 put it there). This will make a MonthView
control available for selection in your ToolBox; so, select it and place one
on the UserForm. The size of the control is set by the Font Size property.
Now, double click the MonthView control and paste this event procedure code
in the window that appears...

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
ActiveCell.Value = MonthView1.Value
Unload UserForm1
End Sub

Next, double-click the sheet you want to pop up the calendar on over in the
VBAProject window on the top/left to bring up its code window. Paste this
code in that window...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Row = 1 And Target.Column = 1 Then
UserForm1.Show
Cancel = True
End If
End Sub

Change the row and column numbers I used above (both 1's) to the row and
column you want the calendar to pop up for. Then save the macro you just
created. Now Run the macro, go back to your Excel sheet and double-click the
cell you set up for this macro above. If everything went will, the UserForm
with the MonthView control on it should appear. Select a date from it and it
should be placed in your cell.

Rick

 

[Ed East]

Thanks to Rick Rothstein for showing us the way. My version of Excel did not
have the Microsoft MonthView Control 6.0 It did have the Calendar Control
10.0 which is what I used. Continuing where he left off... I found that I
could make it so that the calendar pops up for all cells in a column by
simply substituting the = sign for the > sign when telling the program which
rows to target. For example, if you want to be able to double-click all of
the cells on column 1 you'll need to paste the following code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Row > 1 And Target.Column = 1 Then
UserForm1.Show
Cancel = True
End If
End Sub

Similarly, if you want to be able to double-click all the cells along a row
and have a calendar pop-up you'll have to make a similar substitution for the
columns.