Activating a pop up calendar by clicking in a cell

[DarrylW]

Hi,

Don't know if any one can help me.....

I've just added a pop up calendar to a Excel spreadsheet which works fine and pops up when clicking on a button.... But is there any way I can not rely on a button to make the calendar pop but instead make it pop up when I click in to a cell (P6)...?

Thanks.....

[Killian]

This is the kind of thing you can add to your right-click menu by using the Worksheet_BeforeRightClick event to add a menu item to CommandBars("cell")
There's a similar thread here (http://www.vbaexpress.com/forum/showthread.php?t=2714) with some code that should get you started.

[DarrylW]

Thanks mate....


I'm sure this will help

[lucas]

Darryl,
I use this to pop up a calendar. Format the cells according to the script that you want to click on to open the calendar.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.NumberFormat = "m/d/yy;@" Then
If CalendarFrm.HelpLabel.Caption <> "" Then
CalendarFrm.Height = 191 + CalendarFrm.HelpLabel.Height
Else
CalendarFrm.Height = 191
CalendarFrm.Show
End If
End Sub

you will have to adapt it to your script but it shows how you can format specific cells so that when they are clicked, it will call your form. Hope this helps. It is a custom format. m/d/yy;@

ps. the vba goes in the code for the sheet

[DarrylW]

Runs perfect

[lucas]

Darryl,
Your welcome. I use a userform for the calendar because it travels with the excel book. I tried the calendar controls in Excel but everyone doesn't have the same version of excel and I ran in to problems on other computers. PM me if you would like to see the userform.