Trigger Event Macro from Standard Macro

[Rudi]

Hi,

Can I trigger a Worksheet_Change event macro from a standard macro?

IOW:

Code: Select all

Sub ForceUpDate()
   Application.EnableEvents = True
   Call Worksheet_Change
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
   ....
End Sub

TX

[HansV]

A standard macro is stored in a standard module, so it has no direct access to a private procedure in another module.
You can call it though, but you have to specify for which sheet you want to call the Worksheet_Change procedure, and you have to provide the Target argument.

For example:

Code: Select all

    Application.Run "Sheet1.Worksheet_Change", Sheet1.Range("A1")

where Sheet1 is the code name of the worksheet, i.e. the (Name) property.

S188.png

[Rudi]

Excellent, TX.

[rory]

It would be bad design, in my opinion, to ever need to do that. It implies that the code being run by the change event should actually be a routine in a normal module called from both other pieces of code.

[Rudi]

It would be bad design, in my opinion, to ever need to do that. It implies that the code being run by the change event should actually be a routine in a normal module called from both other pieces of code.

The event macro is using EnableEvents = False and then True at end.
Somewhere during the course of testing the code, it must have debugged and never got to running the EnableEvents = True, which meant that the event did not fire when I updated cells in the book. If I ran a standard macro to enable events again, the event handler was fine again.

I am not expecting errors in my code after testing, but I thought I could leave the ForceUpdate Macro in the WB incase there was an issue with the event handler. The user could simply run the Standard macro that switches events back on and runs the handler at the same time...