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

A simple method that works for me is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Address = "$X$5" Then Call macro_name
End sub

'Marco_name is whatever the macro name you want to run. $X$5 is the cell you are clicking on to run the macro. This method I used on the sheet that is being clicked on . This has worked from 2016 to 2019 for my excel. have not tried it on later years.

Answer 4

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