how to access macro code

[Guest]

I have a large number (100) speedsheets which are all similar and make use of
the same vba code for data entry. I dont want to include the code in each
sheet (in case I need to modify it) So the code is in a separate sheet

If I make this an addin then it loads even if the user is doing something
else and doesn't need my code.

If I put a button on a bar or create a keyboard short cut then similarly
this is always available even when not needed

So what is the proper way to have code load automatically and be accesible
when one of my sheets is loaded, but not otherwise.

Many Thanks

 

[Dick Kusleika]

Patrick

One way: Load the add-in automatically, even when not needed. In the
add-in, use application level events to fire whenever a workbook is opened.
In the workbooks that need the add-in, create a Custom Property that will
identify it. In the application level event, check the Custom Property and
create the shortcut/toolbar button if it exists. Use a different
application level event to delete the shortcut/toolbar button when the
workbook is closed.

I prefer to use the Workbook_Activate and _Deactivate events for this. That
way a workbook that doesn't need the add-in can be opened or activated and
those add-in specific controls are hidden.

 

[Guest]

Thank you - most helpful

I have it working except...
To do application events I understand you need a class module and this needs
to be initialised.

Help says
Dim X As New EventClassModule

Sub InitializeApp()
Set X.App = Application
End Sub

but I need to run InitialiseApp when my addin starts

How do I do this??

I can do it manually but not when my addin starts


Thank you

Patrick

 

[Dick Kusleika]

Patrick

In a standard module, put

Dim clsAppEvent as EventClassModule

In the add-in's ThisWorkbook module, put this in the Open event

Set clsAppEvent = New EventClassModule
clsAppEvent.App = Application

Whenever the add-in is opened, the Open event will run and the class will be
initialized.