Excel VBA BeforeDoubleClick not working after other code is run

Question

When I first open up the Excel file, as well as after I click on a button to import additional sheets into the file, the BeforeDoubleClick event works (it adds 'ticks' to cells so the user can see how far they have got in a process). However, it fails to work after I have run additional further code during the process. I have tried changing the Cancel to both True and False but that makes no difference. I also tried to 'call' the event after the additional code, but again, it fails to work.

Here is the BeforeDoubleClick code:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("T4, T11, T17, T26, T30, T39, T43, T48")) Is Nothing Then
   '  Application.EnableEvents = False
        If ActiveCell.Value = ChrW(&H2713) Then
            ActiveCell.ClearContents
        Else
            ActiveCell.Value = ChrW(&H2713)
        End If
        Cancel = False
    End If
    ' Application.EnableEvents = True
End Sub

The code to call the event at the end of the next code is run:

Call Sheets("Front_Page").Worksheet_BeforeDoubleClick(Nothing, False)

Help most gratefully received, thanks.

Call Sheets("Front_Page").Worksheet_BeforeDoubleClick(Nothing, False): as Target is nothing, nothing will happen. — Ike
– Ike, Commented Nov 13 at 13:17
Thank you. I changed that to Call Sheets("Front_Page").Worksheet_BeforeDoubleClick(Range("T4, T11, T17, T26, T30, T39, T43, T48"), False) but didn't work. Will look at answer below. — Sally Parkes
– Sally Parkes, Commented Nov 13 at 13:59

FunThomas · Accepted Answer · 2025-11-13 14:03:28Z

7

Ike mentioned in the comments the reason why nothing is happening:
The Doubleclick-method expects a Range as first argument (name of the parameter is Target). This is the cell the user double-clicked on.

In your event routine, you check if that cell is one of the cells T4, T11... If not, the routine does nothing.

Now you call the event routine with Nothing as first parameter. As Nothing is obviously not any of the cells, nothing happens.

Depending on your code, you could call your routine with a valid cell, for example

Dim cell As Range
set cell = Sheets("Front_Page").Range("T11")
Call Sheets("Front_Page").Worksheet_BeforeDoubleClick(cell, False)

However, I would give you the advice to never call an Event routine. Instead, create a common Subroutine and call that from both places. The following routine should be put into a regular module:

Sub ToggleCheckmark(cells As Range)
    Const checkMark = &H2713
    
    Dim cell As Range
    For Each cell In cells
        If cell.Value = ChrW(checkMark) Then
            cell.ClearContents
        Else
            cell.Value = ChrW(checkMark)
        End If
    Next
End Sub

Your event routine looks like that

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("T4, T11, T17, T26, T30, T39, T43, T48")) Is Not Nothing Then 
        ToggleCheckmark target   ' Don't use ActiveCell here
    End If
    Cancel = False
End Sub

And in your other code, you simply call

ToggleCheckmark Cell   ' Whatever cell you want to toggle

Now what I cannot answer is if your check for the cell address needs to be put into that routine or into the Event routine. If your "other" code already knows that the cell is correct, leave the check in the event routine. Else, move it to the ToggleCheckmark routine.

Update Changed the code so that it can handle multiple cells at once.

edited Nov 13 at 14:03

answered Nov 13 at 13:52

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sally Parkes Nov 13 at 14:39

Thank you FunThomas. I'm definitely learning here (newby). When I use your code, when I've run the next piece ("other") code, it's toggling the entry. What I want it to be able to do is have the functionality of the user being able to double click into the field and it change. This is possible prior to this code being run but after it has been run, when they double click in the field (T4, T11 etc), they just enter the cell and the event doesn't fire.

FunThomas Nov 13 at 15:15

Have you by any chance the statement Application.EnableEvents = False somewhere in your code and never set it back to True?

Sally Parkes Nov 13 at 15:39

That was it! You are a star, thank you very much. Have a good rest of the day. :-)

Frank Ball Nov 13 at 22:05

One more step is implement error handling, so that no matter what happens, EnableEvents gets reset back to "True". Otherwise, something unexpected will happen and you'll be left in bad state.

Collectives™ on Stack Overflow

Excel VBA BeforeDoubleClick not working after other code is run

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related