vba - Handle worksheet change event before running macro

user_2886877 0
Viewed 1526

Assume an Excel worksheet with two vba Sub procedures:

  • Sub1: A handler on worksheet changes

    Private Sub Worksheet_Change(ByVal Target As range)
    'do something
End Sub

  • Sub2: A Sub which runs when clicking a button

When a user clicks the Sub2-button while editing a cell, both Subs are executed in the order Sub2 (because he clicked the button) -> Sub1 (because the edit action was terminated).

How can I reverse this order? I want to handle the worksheet change before running the Sub associated to the clicked button.

Both Subs must remain unrelated. Ie. if the Sub2-button is clicked and no cell editing is going on, only Sub2 should be executed.

vba excel
2 Answers

I came up with this solution:

Private Sub Worksheet_Change(ByVal Target As range)
    'handle worksheet changes here
End Sub

'Workaround to make sure other pending events (like worksheet changes) are handled first.
'It schedules handleButtonClick at the end of the event queue
Public Sub handleButtonClick ()
    Application.OnTime Now, "handleButtonClick_private"
End Sub

Private Sub handleButtonClick_private ()
    'handle button click here
End Sub

It works by deferring the actual handler to the end of the event queue. Therefore, a possible pending worksheet change event will be handled before the actual handler is executed.

user_28868770

It means Sub2 make change in related Sheet of Sub1, Thus...

Use bellow approach:

Private Sub Worksheet_Change(ByVal Target As range)
    Application.EnableEvents = False 'Edited
    If ActiveSheet.Name = "SheetName" Then
        'do something
    End If
    Application.EnableEvents = True 'Edited
End Sub 'Worksheet_Change

Edited

Sub Sub2
Application.EnableEvents = False
    'do something
Application.EnableEvents = True
End Sub 'Sub2

As a result, Sub1 procedure runs only when the related sheet have activated, and when Sub2 procedure is running, another Event procedures have not activated.

user_67925730
Related