Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Trigger macro via doubleclick in cell? (Excel 2002)

    I'm sorry, this is very basic ... I would like to trigger a macro by double clicking in a particular cell. (The cell contains static text.) Is this possible?

    If so, how do I assign this to a macro?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trigger macro via doubleclick in cell? (Excel 2002)

    Right-click the sheet tab.
    Select View Code from the popup menu.
    Create a procedure like this:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ' Your code here
    ...
    ' Cancel the default action
    Cancel = True
    End Sub

    The Target argument of Worksheet_BeforeDoubleClick is a range representing the cell that has been double clicked. You can use this to limit the action to specific cells:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ' Limit action to cells in A14
    If Not Intersect(Target, Range("A14")) Is Nothing Then
    ' Your code here
    ' ...
    ' Cancel the default action
    Cancel = True
    End If
    End Sub

    Warning: Excel users expect that double clicking a cell can be used to edit it.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Re: Trigger macro via doubleclick in cell? (Excel 2002)

    Hans,

    Very helpful, as always, thank you.

    A follow-up question -- is it possible to trigger

    macro A on the first double click,
    macro B on the second double click,
    macro A on the third doubleclick,
    macro B on the 4th, ad infinitum?

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Trigger macro via doubleclick in cell? (Excel 2002)

    You can use a Static boolean variable:
    <pre>Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Static blnFirst as Boolean
    ' Limit action to cells in A14
    If Not Intersect(Target, Range("A14")) Is Nothing Then
    ' Your code here
    If blnFirst Then
    MacroB
    Else
    MacroA
    End If
    blnFirst = Not blnFirst
    ' Cancel the default action
    Cancel = True
    End If
    End Sub
    </pre>


    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Re: Trigger macro via doubleclick in cell? (Excel 2002)

    Thanks very much to you to Rory

  6. #6
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Re: Trigger macro via doubleclick in cell? (Excel 2002)

    Rory (or anyone else following this thread):

    The code you gave me worked very well. However, I'm having a problem making it work on a second, similar macro on the same worksheet.

    What I've tried is this: I copied your Private Sub onto the same page (i.e. just below the first one) and changed the name to

    Private Sub Worksheet_BeforeDoubleClick2(ByVal Target As Range, Cancel As Boolean)

    (Notice the only difference between the names is the "2" after "DoubleClick").

    I then specified a new range (E5:G6) for this to effect, and used a slightly different macro A and macro B (different in that they effect the new range).

    Otherwise, the code is identical. Problem is, nothing happens at all.

    I know the new range acts as a potential trigger for macros because I tried changing the name of the new private sub to the name of the original one, and when I double-clicked the new range, I got a VBA error message about an ambiguous name, which makes sense.

    What am I doing wrong?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trigger macro via doubleclick in cell? (Excel 2002)

    BeforeDoubleClick is an event of the Worksheet object defined by Microsoft. The event procedure that handles this event is named Worksheet_BeforeDoubleClick. This is a "fixed" name, not one I or Rory made up.

    There is no defined event BeforeDoubleClick2, so Worksheet_BeforeDoubleClick2 does nothing.

    Instead, add the extra code to the existing Worksheet_BeforeDoubleClick procedure:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Static blnFirstA1D4 as Boolean
    Static blnFirstE5G6 As Boolean

    ' Limit action to cells in A14
    If Not Intersect(Target, Range("A14")) Is Nothing Then
    ...
    End If

    ' Limit action to cells in E5:G6
    If Not Intersect(Target, Range("E5:G6")) Is Nothing Then
    ...
    End If
    End Sub

    You can add more such sections if needed.

  8. #8
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Re: Trigger macro via doubleclick in cell? (Excel 2002)

    Aha! That explains a lot. I hope you at least got a laugh out of my mistake -- I did. Anyway, Hans, sorry about this: I tried implementing your code and seem to have got tangled up in my "If / End if " hierarchy.

    Here is my code (sorry it's so bloated, it was recorded, and I'm unsure which bits to cut out) in the attached text file. Can you show me how to fix the Ifs and End ifs please?
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trigger macro via doubleclick in cell? (Excel 2002)

    Consistent indentation helps to recognize which If ... Then belongs to which End If.
    Also, I'd put most of the code in separate procedures that are called from the Worksheet_BeforeDoubleClick procedure. In the attached file, they're all together, but you could put the separate procedures in a standard module (the kind created by selecting Insert | Module).
    I've modified the code so that no cells are selected - this is usually more efficient.
    Attached Files Attached Files

  10. #10
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Re: Trigger macro via doubleclick in cell? (Excel 2002)

    Hans,

    Thanks. As usual, your code runs like a dream.

    I also concede your points about the state of my code. My problem is I tend to try to use VBA when I need it, concentrating on individual features, without getting a foundation in basics like those you pointed out. I know it's not the way to go about it. I'll try harder to get the fundamenetals in place.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •