5

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.

Ƭᴇcʜιᴇ007
  • 111,883
  • 19
  • 201
  • 268
Noob Doob
  • 435
  • 1
  • 8
  • 22

3 Answers3

8

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.

teylyn
  • 22,498
  • 2
  • 40
  • 54
  • Right on the spot. – Noob Doob Oct 22 '13 at 10:39
  • Is there a way to do this so that only mouse clicks create the change, so moving over the cell with the arrow keys doesn't fire the macro? At the moment I'm using OnDoubleClick, but that's a little tedious. – CLockeWork Oct 22 '13 at 10:49
  • @CLockeWork, this is a different question. Why don't you post a new question. You can link to this one for reference and background. – teylyn Oct 22 '13 at 10:56
  • As I tested this, the macro is NOT fired by moving the pointer over the cell. The click is needed too. – Noob Doob Oct 22 '13 at 12:27
  • 1
    Yes, that is what you specified in the question: "clicking once on a specified cell". – teylyn Oct 22 '13 at 21:09
  • @CLockeWork: did you find the answer to your question, which is actually identical to the original question and did not really get answered by this answer. – peter May 25 '21 at 16:07
0

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
Ray
  • 1
  • 1
-3

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

DEH
  • 7
  • 3
    This is **not** an answer to the original question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient [reputation](http://superuser.com/help/whats-reputation) you will be able to [comment on any post](http://superuser.com/help/privileges/comment). – DavidPostill Feb 04 '15 at 14:53
  • 2
    It's SELECTION change, so yes the click does fire the event, if the cell is not selected already. My workaround was that when the cell was selected, I executed the macro and then changed the selection to a non-important cell (The change was done inside the executed code) (With selection change you cannot click on the same cell more than one time consecuteviley and thus execute the macro multiple times, unless you change the selected cell after you finish) – Noob Doob Feb 04 '15 at 15:52