Excel Add-In

Topic

New Reply

Is there anyway to have an event within the active workbook fire the associated procedure in an Add-In? This needs to happen without any code within the active workbook being used.

Reply | Quote

Replies

Declare a new workbook variable in the add-in, but declare it using the WithEvents keyword. VBA will then allow you to select that variable from the object drop down at the top. However these can only be declared in object modules, e.g. sheet code, or class modules.

Write the appropriate event responses, then at runtime (perhaps trap the WorkBook_Open event of the Application object by this same method?) assign the active workbook to that variable. Events firing for that object will also trigger the event code for the associated variable.

For example, try adding the following code:

Code:

Public WithEvents app As Application
Public WithEvents wbk As Workbook

Private Sub Workbook_Activate()
    Set app = Application
End Sub

Private Sub app_WorkbookActivate(ByVal Wb As Workbook)
    Set wbk = Wb
    Debug.Print "Activated: " & Wb.Name
End Sub

Private Sub app_SheetActivate(ByVal Sh As Object)
    Debug.Print "Application level: " & Sh.Parent.Name & " -> " & Sh.Name
End Sub

Private Sub wbk_SheetActivate(ByVal Sh As Object)
    Debug.Print "Workbook level: " & wbk.Name & " -> " & Sh.Name
End Sub

A good resource to start from is this one.

Reply | Quote

Thank you.

I will certainly give this a workout,

Reply | Quote