Results 1 to 3 of 3

Thread: Excel Add-In

  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.
    Regards
    Don

  2. #2
    New Lounger
    Join Date
    Dec 2009
    Location
    London, UK
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thank you.

    I will certainly give this a workout,
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •