Disable 'Macro' dialog box...

[Ruskin Hardie]

Thanks Geoff, for the info on how to protect the VBA code.

Have one further question; Now that the workbook and code is protected, I
have sub routines that are run from command buttons. However, it is still
possible to push ALT-F8 to list my routines, then click on a routine, then
choose 'Run'...

Is there a way to disable this option, so that users can not view or run the
list of available routines (I need them to run the routines, only from the
command buttons).

 

[Otto Moehrbach]

Ruskin
You can do what you want by making each macro a Private macro. This
will preclude that macro from appearing in the Tools - Macro - Macros list
of macros.
You make a macro a Private macro by preceding the sub name with
"Private". For instance:
Private Sub MyMacro()

If you want to make every macro in a module Private, you can put:
"Option Private Module" (without the quotes) at the top of the module
instead of putting Private in each sub. HTH Otto

 

[Ruskin Hardie]

Thanks Otto...

Issue, is that my code is very modular.... Have about 15 modules and the sub
routines are scattered all over them. So one sub routine in module A
(activated by the button) may run a sub routine in module B, that has no
parameters passed. If I make the sub routine in module B private, then the
routine in module A can not call it...

I have managed to work around this, by having one module that has all the
'private' sub routines (that are activated by the buttons). All other
routines in all other modules are public, but I pass a dummy variable to
them, so that they have optional input parameters and do not come up in the
macro list. It has meant a bit more work, but it appears to do the trick...

EG:

Module A;

Private Sub actButtonSave()
ActivateSave
End Sub

Module B;

Sub ActivateSave(Optional dumBool As Boolean)
.....
.....
End Sub

 

[Otto Moehrbach]

Ruskin
You have a bit of a problem but there is a way around it. As you know,
any macro that has a parameter passed to it will not appear in the list of
macros, and that is what you want.
What you need to do is pass an innocuous parameter to the called macro
and setup the called macro to take a passed parameter. The code in the
called macro need not use the passed parameter. For instance:
Private Sub Macro1()
Call Macro2(5)
End Sub

Sub Macro2(x As Integer)
'Your code need not use x
End Sub

HTH Otto