Call Private Macros

Posted by Frank on December 28, 2001 8:52 PM

I need a way to run a Private Macro on workbook activate and workbook deactivate. I need to make the macros private so that the end user cannot manually run them or delete them. Is there another way to do this besides making them private? When I want to run the macro I have to use the workbook name, but what if the name changes, them the code wont be able to find the macro. I use application.run (activeworkbook.name & "MyMacro") which works fine for the workbook activate, but for the workbook deactivate it returns the new workbook name. How do I get the workbook name before it deactivates. Is there anyway I can get save the workbook name in memory as long as the workbook is open? I cant put the value in a cell since I couldnt refer to it without the workbook name.

Any help would be greatly appreciated.

Thanks

Frank

Posted by Gary Bailey on December 29, 2001 4:35 AM

The Workbook Activate and Deactivate event procedures will be private by default. Perhaps it is a macro you call from these that is public? If you want to stop a public macro from appearing in the Macro Dialog box in Excel then one way is to declare it with a "dummy variable". The Macro Dialog doesn't show any macros that require arguments to run (because you can't supply them from the Macro Dialog).

Sub MacroYouWantToHide(Dummy)
' code
End Sub

You have to remember to supply the argument when you call it from your code though - even though it doesn't do anything.

As to your other problems have you tried using the ThisWorkbook object - this refers to the workbook the code is running in regardless of which is currently active. In fact in the ThisWorkbook code module you can use the Me keyword instead.

Gary

Posted by Ivan F Moala on December 29, 2001 4:06 PM

Just make the Module Private and call your
routines as normal

eg. In your Module code add;
Option Explicit
Option Private Module

Sub test1()
MsgBox "test1 is running:"
End Sub

Sub test2()
MsgBox "test2 is running:"
End Sub

Sub test3()
MsgBox "test3 is running:"
End Sub

To reference these routines ref as normal eg

Private Sub Workbook_Activate()
test1
test2
test3
End Sub

Private Sub Workbook_Deactivate()
test1
End Sub

The Option Private Module makes all
your routines in that module not declared Private available to other modules in the project, but not to other projects or applications. They do not
List in the run macros option.

Ivan