Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Aug 2012
    Location
    Israel
    Posts
    25
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Solution: How to run an event by double-clicking an excel cell

    This one works! I did not use complicated lines with ISNOT. It is therefore simple.
    Want to start one of two possible events by clicking on certain places in excel?
    Here is how:
    Open your vba project modules by Alt-F11 - as usual.

    Under VBA project you can see a list of each of your spreadsheets by name and number.

    For each sheet that should run your event(s) enter a code like this. DoubleClick any cells from A8 to A26 will cause "remove" procedure to start. DoubleClick any cells from J8 to J26 will cause the "recover" procedure to start. These two procedures have modules above under "PERSONAL.XLSB". Double click anywhere else will cause excel to do its normal thing, that is, to enter the cell for editing.

    This simple procedure seems not to exist on the internet. I find it very useful.

    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim ActiveRow As Integer
    Dim ActiveColumn As Integer
    
    ActiveRow = Target.Row
    ActiveColumn = Target.Column
    
    If ActiveRow < 8 Or ActiveRow > 26 Then Exit Sub
    
    If ActiveColumn = 1 Then
    Cancel = True
    Call Remove
    Else
    Cancel = False
    End If
    
    If ActiveColumn = 10 Then
    
    Cancel = True
    Call recover
    Else
    Cancel = False
    End If
    
    End Sub
    Last edited by RetiredGeek; 2014-12-04 at 11:07. Reason: Added Code Tags

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Michael,

    Here's a slightly different way to code the logic that is easier to extend to more cases if necessary.
    All you need to do is add a range to the Set rngIsect command then add a Case to the Select Case construct.
    Code:
    Option Explicit
    
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
       Dim rngIsect  As Range
       
       Set rngIsect = Application.Intersect(Range("A8:A26,J8:J26"), Target)
       
       If rngIsect Is Nothing Then    'Double-Click not in ranges Exit
            Cancel = False
       Else
       
         Select Case Target.Column
            Case 1                        'A-Range Detected
               Cancel = True
    '           Call Remove
               MsgBox "Call Remove Procedure", vbOKOnly + vbInformation, _
                      "Range A8:A26"
           Case 10                        'J-Range Detected
               Cancel = True
    '           Call Recover
               MsgBox "Call Revocer Procedure", vbOKOnly + vbInformation, _
                      "Range J8:J26"
         End Select
         
       End If
    
    End Sub
    Last edited by RetiredGeek; 2014-12-04 at 16:30. Reason: Changed a comment only
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Michael Unger (2014-12-04)

  4. #3
    Lounger
    Join Date
    Aug 2012
    Location
    Israel
    Posts
    25
    Thanks
    6
    Thanked 0 Times in 0 Posts
    RG,
    Thank you!

    Of course you are correct! I just couldn't find enough information on the internet and tried a few simple methods until I got something that worked.

    I am actually interested in other ways to do this to make use in the future.

    Michael

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Michael,

    Here is an expansion to RG's fine code. By pressing Ctrl-Shift-Z you can toggle between 2 different cursors (Default and Northwest Arrow). Depending on the active cursor (mode), double clicking the columns 1 or 10 will run a different set of macros. For example, in the default cursor mode, double clicking A10 will run "Remove". Whereas, if the active cursor is Northwest Arrow, clicking A10 will run "Retry". The same with the cells in Column 10, Default and Northwest Arrows will run "Recover" and "Resend" macros respectively. See images below.

    HTH,
    Maud

    ChangeCursors.png

    Place in the sheet module:
    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    '----------------------------
    'SET ROW BOUNDRIES
    With Target
    If .Row < 8 Or .Row > 26 Then Exit Sub
    '----------------------------
    'DETERMINE ACTIVE CURSOR = DEFAULT
    If Application.Cursor = xlDefault Then
    '----------------------------
    'EVALUATE MACRO TO RUN BY COLUMN
        Select Case .Column
            Case 1
                Cancel = True
                Call Remove
            Case 10
                Cancel = True
                Call Recover
            Case Else
                Cancel = False
        End Select
    '----------------------------
    'DETERMINE ACTIVE CURSOR = NOT DEFAULT
    Else:
    '----------------------------
    'EVALUATE MACRO TO RUN BY COLUMN
        Select Case .Column
            Case 1
                Cancel = True
                Call Retry
            Case 10
                Cancel = True
                Call Resend
            Case Else
                Cancel = False
        End Select
    End If
    End With
    End Sub
    Place in a standard module:
    Code:
    Public Sub ChangeCursor()
    If Application.Cursor = xlDefault Then
        Application.Cursor = xlNorthwestArrow
    Else:
        Application.Cursor = xlDefault
    End If
    End Sub
    Place in ThisWorkbook Module:
    Code:
    Private Sub Workbook_Open()
    Application.OnKey "+^Z", "ChangeCursor"
    End Sub
    Attached Files Attached Files

  6. #5
    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
    FWIW I would not recommend putting Cancel = False without good reason. There may be other event handlers in place that have good reason for Cancelling the default behaviour and doing this could override that.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #6
    Lounger
    Join Date
    Aug 2012
    Location
    Israel
    Posts
    25
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Thank you, but...
    I do not know why it is set up this way, but think of it like this:
    You make a double-click on an excel cell - this causes an the curser to enter the cell as "edit". We are used to this.

    This event is stopped by the program "Worksheet_BeforeDoubleClick," which tells Excel "Wait a second - the user wants to do something else!"

    But I want to do something else only if the click is between A8 & A26 or between J8 & J26. Therefore I must tell my tiny program that the user wants SomethingElseBesidesEdit only if click is on certain cells. Otherwise I want excel to do its normal "thing" which is Edit. Grandpa Excel provided us with the lines Cancel = False and Cancel = True.

    For a long time I used this procedure without Cancel=False or True (slightly different programing) and I simply didn't like it. You see, I want Excel to act like Excel except under certain conditions. I discovered that the secret to tell Excel how to act is embedded in the Cancel=False or True lines.

    I hope I explained this well. Try it both ways and decide for yourself.

    Michael

  8. #7
    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
    You're missing my point, I'm afraid.

    The only thing that will change Excel's default behaviour is setting Cancel = True. That's fine, and your code should do it if you want to prevent the default behaviour (i.e. entering Edit mode in this case).

    What you should not do is set Cancel = False without very good reason, because this might override another event handler that had previously set it to True.
    (Note that under ordinary circumstances there is no need to set Cancel to False because that is its initial value anyway.)
    Regards,
    Rory

    Microsoft MVP - Excel

  9. The Following User Says Thank You to rory For This Useful Post:

    Michael Unger (2014-12-07)

  10. #8
    Lounger
    Join Date
    Aug 2012
    Location
    Israel
    Posts
    25
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Thank you!

    Just to make sure, I tested it without the Cancel=False and you are correct as always.

    Michael

Posting Permissions

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