CODEWITHSUNDEEP
vbaexcel
Piloos
Piloos

Reputation: 67

vba - Handle worksheet change event before running macro

Assume an Excel worksheet with two vba Sub procedures:

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.

Upvotes: 3

Views: 2191

Answers (2)

Piloos
Piloos

Reputation: 67

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.

Upvotes: 3

mgae2m
mgae2m

Reputation: 1142

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.

Upvotes: 0

Related Questions