Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to access macro code
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 -- Patrick |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to access macro code
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. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Patrick" wrote in message ... 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 -- Patrick |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to access macro code
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" wrote: 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. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Patrick" wrote in message ... 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 -- Patrick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Access in Excel VB Code | Excel Discussion (Misc queries) | |||
How can I prevent access to code contained within Tab | Excel Worksheet Functions | |||
Is there somewhere I can access Excel-VB code at once? | Excel Programming | |||
Where to put code for access by all workbooks | Excel Programming | |||
XL2000 VBA code to ACCESS 2000 | Excel Programming |