Run Macro on Click

[mikeellinas]

Hi. First post here. I tried searching, but did not see anything (I may not have used the proper search terms).

I am new to the VBA game. I have a macro using the MonthView form. It is triggered by a keyboard shortcut:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnKey "+^{C}"
Application.CommandBars("Cell").Controls("Insert Date").Delete
End Sub
Private Sub Workbook_Open()
On Error Resume Next
Dim NewControl As CommandBarControl
Application.OnKey "+^{C}", "Module2.OpenCalendar"
Application.CommandBars("Cell").Controls("Insert Date").Delete
Set NewControl = Application.CommandBars("Cell").Controls.Add
With NewControl
.Caption = "Insert Date"
.OnAction = "Module2.OpenCalendar"
.BeginGroup = True

End With

End Sub



I would like to trigger the macro when clicking any cell in the range C8:C13 or C15:C20. I would even be okay if it was done when clicking anything in C8:C20. Can anyone help me with the code for this adjustment?


EDIT: I forgot to mention. Using Excel 2010 if that matters

 

[bsquad]

you are missing the actual code, what you have posted is the "action" the actual code Module 2 and is named OpenCalendar
Easiest thing would probably be to insert a shape (INSERT TAB --> Shapes) pick one that looks like a button or whatever you want right click it and select 'Assign Macro' and pick your OpenCalendar macro. If you don't see it there its marked as a 'Private sub meaning you have to go in VBE in Module 2 and change it from "Private Sub OpenCalendar()" to "Sub OpenCalendar()"

 

[mikeellinas]

Thanks for the reply! I was able to successfully put a shape there and do what you suggested. However, I was hoping to trigger the macro when someone clicks the cell (the cell already has text in it that is now blocked by the shape). Is there a way to do that?

 

[Logit]

.

Here is a Calendar control project with a few more options. Already programmed to enter date into ANY cell. Presumably you could edit the
code to restrict to the range/s you desire.


Example project download at bottom of page :

https://sites.google.com/site/e90e50/calendar-control-class

 

[bsquad]

you could just make the shape completely transparent, but was there another underlying reason for the mouse click? by mouse click do you mean single click double click to edit the cell?
because you could always do a ByVal sort of macro, but if its just because of a mouse click I would just suggest to use a transparent shape it would almost be unnecessary code if there is no other reason.

i.e.

(ByVal Target As Range)

 

[mikeellinas]

thanks. I cannot download and install on my work computer though

 

[mikeellinas]

I actually ended up making the shape transparent on my own. Good two see I was thinking like you . This worked fine. Thanks for the help!