How to run a macro upon a cell click

[Banzai]

I have a macro that runs a pop-up calendar and applies the date selected
from within the calendar to the currently selected cell.

As default I have to run this manually via tools> macro>macros>run.

My question is, how do I force this so that as soon as the user clicks on
the cell that particular macro (OpenCalendar) is run and the calendar is
opened for populating the cell that has just been clicked.

If possible i'd also like to limit any user entry for certain cells other
than that populated by the calendar macro.

Thanks in advance

 

[Guest]

Right click the sheet tab and select view code. This take you to the VBE.
Just above the Code Window are 2 drop down combo's. Change the one on the
left to Worksheet. This gives you access to worksheet event code. The
different events are listed in the drop down on the right. When you select
one it will drop a code stub in for that event. Note that for selection
change there is an arguement Target which is the cell(s) that were just
selected...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Target.Address
End Sub

So you want something like...
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" then Call OpenCalendar
End Sub

 

[Gord Dibben]

See how Ron de Bruin does it.

http://www.rondebruin.nl/calendar.htm

Note the "more information" link to download mscal.ocx if needed.


Gord Dibben MS Excel MVP

 

[Banzai]

See how Ron de Bruin does it.

http://www.rondebruin.nl/calendar.htm

Note the "more information" link to download mscal.ocx if needed.


Gord Dibben MS Excel MVP

Seen that, but I don't (think) it answers my question