PDA

View Full Version : Worksheet activate code in Add-in



Little_pea
06-23-2008, 12:35 PM
I have a spreadsheet (call it SS.xls) with a number of sheets (called, eg, "Notes","Archive"), and a separate add-in.

I want to have some code in the add-in that is called when one of the sheets is activated. (separate modules for each of the sheets)
Of course, I could have some code in SS.xls, but I want it all in the add-in, as there's going to be lots of versions of the spreadsheet, and I don't want to have to recall the spreadsheets if some code changes are needed.

How can I get code in the add-in to tie in to the Worksheet_Activate event?
I know I've done it before, but can't remember how I did it!

greymalkin
06-23-2008, 01:54 PM
can't you just create a sub in the add-in and call that sub from the worksheet_activate event? As long as the name of the sub never changes you will only have to modify the code in the add-in and not the actual .xls file.

xld
06-23-2008, 02:09 PM
Either:

in that activate event call the addin procedure directly using APplication.Run (documented in VBA Help)

Or

set a reference in the workbook to the addin and call the procedure directly.

Little_pea
06-24-2008, 04:33 AM
Many thanks.

I've used the Worksheet_Activate event to call the add-in sub, with complete success.