Trigger macro with ONE click on specific cell

Question

Would you mind pointing a way to start a macro in Excel 2010 by clicking only once on a specified cell? I had seen a solution somewhere, but now I can't trace it back.

Answer 1

The following code will fire when cell D4 is clicked in the worksheet.

Right-click the sheet tab and select "View Code". Paste this into the code window:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("D4")) Is Nothing Then
            MsgBox "Hello World"
        End If
    End If
End Sub

Adjust the cell reference from "D4" to reflect your desired cell. Replace the MsgBox line with your desired code.

Answer 2

Here is a slightly different approach to the original question, which may suit some applications.

' Make the desired cell a hyperlink to itself ...
With ThisWorkbook.Sheets(mysheet)
  .Hyperlinks.Add Anchor:=.Cells(myrow,mycol), Address:="", SubAddress:="R[0]C[0]"
End With

' ... and then let the handler for the FollowHyperlink event do the business: 
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
  Debug.Print "clicked " & Target.TextToDisplay & " on row " & Target.Range.Row
End Sub

Answer 3

The Worksheet_SelectionChange event does NOT fire unless the cell value is changed. Just clicking on the cell does NOT fire the event.