Calendar drop down

[Rich D]

OK. Everybody refers to Ron de Bruin for how this is done. I do it...a
calendar appears and that's it. I can't make a selection, I can't get rid of
the calendar even if I click on another cell. What am I doing wrong? Thanks.

 

[Ron de Bruin]

Hi Rich

Which Excel version ?

I think you are in Edit mode

 

[Rich D]

Ron,

Using Excel 2003, windows XP. When I open the calendar control, the box
with the triangle (DESIGN?) shows too.
--
Rich D
Armstrong Custom Homes
Redmond

Hi Rich

Which Excel version ?

I think you are in Edit mode

 

[Rich D]

Also Ron,

When I click Insert>Object, there are 2...one says Calendar Control and
another says Calandar Control 11.0.
--
Rich D
Armstrong Custom Homes
Redmond

Hi Rich

Which Excel version ?

I think you are in Edit mode

 

[Ron de Bruin]

Maybe you have another program installed that install this control
After you insert it do you see the "Exit Design mode" button on your screen ?

Click on it to exit design mode
Now the event code will work on the control



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Also Ron,

When I click Insert>Object, there are 2...one says Calendar Control and
another says Calandar Control 11.0.

 

[Rich D]

Ron,

I've been trying everything to get this to work. Now, when I
Insert>Objcet>Calendar control, it says, "Cannot insert object".
--
Rich D
Armstrong Custom Homes
Redmond

Hi Rich

Which Excel version ?

I think you are in Edit mode

 

[Rich D]

I click Insert>Object>Calendar Control. A calendar appears along with the
design mode box. Whether I X it or not, the calendar stays, and no selection
as to actual date is made.

 

[Ron de Bruin]

Have you copy the code from my site in the correct place
http://www.rondebruin.nl/calendar.htm

 

[Rich D]

Ron,

I'm a complete neophyte when it comes to code. No, I have done no copying
of code as yet mainly cuz it wasn't really clear to me what to put where.
It's hard for me to believe that this isn't available in Excel, on a toolbar
like everything else is. I appreciate your help. This really has me bugged.

 

[Ron de Bruin]

Read the page and follow the steps and post back if you have problems

 

[Rich D]

I'm beginning to think it would be easier just to enter the date manually. I
pasted the code as suggested. then, as soon as I click ANY CELL in the
worksheet I get: "runtime error '424'...Object Required. Sorry to be such a
pest.

 

[Ron de Bruin]

Start again
Open a new workbook

Excel 97-2003
Use Insert-Object on the Worksheet Menu Bar.
Select the control in the list and press OK.


You must copy the code below in the Worksheet module.
Right click on the sheet tab and choose view code.
Paste the code in the sheet module that is active now and press Alt-Q to go back to Excel.


Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "mm/dd/yyyy"
ActiveCell.Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("A1:A20"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
' select Today's date in the Calendar
Calendar1.Value = Date
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub


If you select a cell in the Range A1:A20 the Calendar will popup and when
you Click on the calendar the date will be placed in the active cell.
If you select a cell outside the range the Calendar will disappear.

 

[Rich D]

Ron,

Thanks a bunch!!! I guess I had things in the wrong sequence. This will
work. I assume that anytime I want to put a calendar drop down in a
worksheet, I will have to go through these steps. Right?

thanks again.

 

[Ron de Bruin]

I will have to go through these steps. Right?

Correct