How can I manually trigger the Activate event of a worksheet in VBA?-CodePudding

I have a worksheet with:

Private Sub Worksheet_Activate()
    MsgBox "Ran"
End Sub

I have a button on my toolbar that I made. What I want it to do is trigger this method on the currently selected WorkSheet.

I figured I could do Call ActiveWorksheet.Activate or Call Worksheet.Activate but while these seem to execute without errors, the method is not called.

As a workaround I considered adding a public DoActivate method, but it seems a bit lame and I would likely have to fiddle with CallByName to get it to work (and developers would have to remember to implement this method on every worksheet).

Is there a reason why my Activate method is not calling manually via the above code, or a suitable workaround to get what I'm looking for?

CodePudding user response：

You can call the activating event of any active sheet (without knowing its name) in this way:

Create the next event in ThisWorkbook code module. Or, simple copy the following code. Take care that such an event does not already exist:

Public Sub Workbook_SheetActivate(ByVal Sh As Object)
    MsgBox Sh.Name & " activated..."
    'the necessary code here...
End Sub

Then, call it from a standard module in the next way:

ThisWorkbook.Workbook_SheetActivate ActiveSheet

If you want excepting some sheets, you can adapt the event code to do it:

If sh.Name <> "MySheetName" then
    MsgBox Sh.Name & " activated..."
    'the necessary code here...
End if

If many sheets should be excepted, an array of sheet names should be built and use Application.Match to differentiate between the sheets to use their event and the other ones.

CodePudding user response：

Move your code to a new Sub called OnActivate

Private Sub Worksheet_Activate()
    OnActivate
End Sub

Private Sub OnActivate() 'or Public if you call from another module
    MsgBox "Ran"
End Sub

The Worksheet_Activate() event handler can be called manually from inside the module by Worksheet_Activate like any other sub (although this is IMO not a nice way to do it)

If you want to ensure all worksheets have the same method, then you can make them Implement an interface: e.g.

Class module: IActivateHandler

Public Sub OnActivate()
End Sub

Then in Sheet1, 2, 3 etc:

Implements IActivateHandler

Private Sub IActivateHandler_OnActivate()
    MeOnActivate
End Sub

Private Sub Worksheet_Activate()
    MeOnActivate
End Sub

Private Sub MeOnActivate() 
    MsgBox "Ran"
End Sub

And the button:

Private Sub Button1_Click()
    Dim sheetToCall As IActivateHandler
    ' Debug.Assert TypeOf ActiveSheet Is IActivateHandler
    Set sheetToCall = ActiveSheet 'gets the IActivateHandler of the sheet, ensuring it is defined. Will error if it isn't
    sheetToCall.OnActivate 'runs the IActivateHandler_OnActivate() method of sheet1
End Sub