Pop-Up Calendar

[Lynda]

I have an Excel 2003 spreadsheet with 3 separate pop-up calendars, basically
there is one at the top, one in the middle and one at the end. I used Ron de
Bruin’s code for his pop up calendar. I changed the code to ‘calendar 2’ and
‘calendar3’ to hopefully get them to work separately but it doesn’t seem to
work. Could someone advise me on what I may be doing wrong.

Lynda

 

[JLatham]

What is not working for you - there are 2 pieces to Ron's code: moving value
from the calendar(s) to the worksheet, and the other way around.

I placed 3 calendar controls on an Excel 2003 sheet and this code worked for
me:

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

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

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

Did you put the code into the worksheet's code module? Choose the sheet
with the calendar controls on it and then right-click the sheet's name tab
and choose [View Code] from the list. The code module for the worksheet will
appear and that is the module that you should put the code into.

 

[JLatham]

On the off chance that your calendars were given names other than Calendar1,
Calendar2 and Calendar3 you can use this code to determine what their names
on the sheet are:

Sub GetShapesNames()
Dim anyShape As Shape
For Each anyShape In ActiveSheet.Shapes
MsgBox anyShape.Name
Next
End Sub

You can put that code into a standard code module or even into the
worksheet's code module and run it from there using [F5] while in the VB
Editor. It will show you the names of all shapes on the active sheet, one at
a time.

What is not working for you - there are 2 pieces to Ron's code: moving value
from the calendar(s) to the worksheet, and the other way around.

I placed 3 calendar controls on an Excel 2003 sheet and this code worked for
me:

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

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

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

Did you put the code into the worksheet's code module? Choose the sheet
with the calendar controls on it and then right-click the sheet's name tab
and choose [View Code] from the list. The code module for the worksheet will
appear and that is the module that you should put the code into.

I have an Excel 2003 spreadsheet with 3 separate pop-up calendars, basically
there is one at the top, one in the middle and one at the end. I used Ron de
Bruin’s code for his pop up calendar. I changed the code to ‘calendar 2’ and
‘calendar3’ to hopefully get them to work separately but it doesn’t seem to
work. Could someone advise me on what I may be doing wrong.

Lynda

 

[Lynda]

Thank you 'J', I have used Ron's code before on many other sheets and the
'Calendar1' code is working fine, but for some reason when I change it to
Calendar2 and Calendar3 these 2 don't want to work. I have a lot of code on
sheet1 those being code for triple drop-downs and then more code for a single
drop-down. If there is too much code on one sheet will that start to affect
the operation of the other codes?

On the off chance that your calendars were given names other than Calendar1,
Calendar2 and Calendar3 you can use this code to determine what their names
on the sheet are:

Sub GetShapesNames()
Dim anyShape As Shape
For Each anyShape In ActiveSheet.Shapes
MsgBox anyShape.Name
Next
End Sub

You can put that code into a standard code module or even into the
worksheet's code module and run it from there using [F5] while in the VB
Editor. It will show you the names of all shapes on the active sheet, one at
a time.

What is not working for you - there are 2 pieces to Ron's code: moving value
from the calendar(s) to the worksheet, and the other way around.

I placed 3 calendar controls on an Excel 2003 sheet and this code worked for
me:

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

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

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

Did you put the code into the worksheet's code module? Choose the sheet
with the calendar controls on it and then right-click the sheet's name tab
and choose [View Code] from the list. The code module for the worksheet will
appear and that is the module that you should put the code into.

I have an Excel 2003 spreadsheet with 3 separate pop-up calendars, basically
there is one at the top, one in the middle and one at the end. I used Ron de
Bruin’s code for his pop up calendar. I changed the code to ‘calendar 2’ and
‘calendar3’ to hopefully get them to work separately but it doesn’t seem to
work. Could someone advise me on what I may be doing wrong.

Lynda