Exceute macro when cell is clicked

[Kent McPherson]

How do I execute a specific macro when I click on a cell? For example, if I
click in cell B3, I want to run a macro. Suggestions?

 

[Paul C]

Use the Worksheet_SelectionChange method.\

This would go on the individual sheet in the VBA Editor

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> "$B$3" Then Exit Sub
Do Stuff
End Sub

 

[Gary''s Student]

One limitation of Paul's approach is that the macro would get also get called
if the cell were entered via the ARROW keys or the TAB key or even the enter
key.

Another approach is to Insert a hyperlink in B3 to a Place in the Document
(namely B3). At first blush, this seems a little silly (why insert a
hyperlink that goes nowhere?). We now can trap the click with a hyperlink
follow event macro:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Target.Parent.Address = "$B$3" Then
Call hello
End If
End Sub

Now the cell will perform just like a button. You can even add a mouse-over
popup message if you want.

 

[Kent McPherson]

Yes, this is great. Thanks.

Use the Worksheet_SelectionChange method.\

This would go on the individual sheet in the VBA Editor

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> "$B$3" Then Exit Sub
Do Stuff
End Sub

 

[Paul C]

That is a good point. This is a more robust method.

 

[Kent McPherson]

Very cool. I love this newsgroup!

One limitation of Paul's approach is that the macro would get also get
called
if the cell were entered via the ARROW keys or the TAB key or even the
enter
key.

Another approach is to Insert a hyperlink in B3 to a Place in the Document
(namely B3). At first blush, this seems a little silly (why insert a
hyperlink that goes nowhere?). We now can trap the click with a hyperlink
follow event macro:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Target.Parent.Address = "$B$3" Then
Call hello
End If
End Sub

Now the cell will perform just like a button. You can even add a
mouse-over
popup message if you want.