Worksheet Events with an Add-in 1

[mlohmac]

Hello,

I have a question for you Excel VBA programmers out there. I have recently written a small application that fills down columns based on various criteria. The application is packaged as an Add-in for easy distribution. The users of this Add-in will be using its functionality with various *.xls files.

Since there will be many different *.xls files used with the add-in I don't think it is possible for me to place code in the "Worksheet_Activate" event associated with the worksheets. (Since this is not saved with teh Add-in) However, I do want to execute some code in an open UserForm whenever the user activates a different worksheet.

Does anyone have any idea how to do this? Where should I place my code to take advantage of this event? Do I need to dynamically create links from the worksheets to objects I can manipulate in code? Help!

-Thanks
Cameron Holm

 

[combo]

Hi Cameron,
I think that you have two possibilities:
- assign a macro in your add-in to a keystroke or menu item created when you open your add-in, and execute it on demand; if you do not refer to a workbook/worksheet, it will operate on active sheet.
- activate application events and use them to deal with sheets/worbooks.

To activate application's worksheet/workbook events, you have to:

1. add in your add-in class module (and name it say clsAPP),

2. describe events in it, for instance (when you select appEvents from the left drop-down, you have access to events in right drop-down list, procedure headers are created automatically):
[tt]Public WithEvents AppEvents As Application

Private Sub AppEvents_SheetActivate(ByVal Sh As Object)
' event description - do something with sheet activated (Sh)
End Sub

Private Sub AppEvents_WorkbookActivate(ByVal Wb As Workbook)
' event description - do something with workbook activated (Wb)
End Sub[/tt]

3. declare and assign to variable new instance of the class (in standard module of add-in):
[tt]Dim appXl As New clsApp

Sub init()
Set appXl.AppEvents = Application
End Sub[/tt]

4. execute initializing procedure (for instance ThisWorkbook module of the add-in):
[tt]Private Sub Workbook_Open()
Call init
End Sub[/tt]

After initializing application events, this will work for every sheet/workbook activated, so the event description should be carefully planned.

combo

 

[mlohmac]

Combo,

The Application events method described above worked like a charm! Thanks alot!

-Cameron