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.
3 Answers
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.
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
The Worksheet_SelectionChange event does NOT fire unless the cell value is changed. Just clicking on the cell does NOT fire the event.
