Hi

I have written a spreadsheet application that has a lot of macros in it. However they all appear in the run macro list in the tools menu. I fear that someone may run an individual macro. Is there any way to make sure the macros do not show up in that list

Thanks in advanc

Krissy

One way:

Put

Option Private Module

at the top of each regular code module.


In article ,
Kristanna wrote:

I have written a spreadsheet application that has a lot of macros in it.
However they all appear in the run macro list in the tools menu. I fear that
someone may run an individual macro. Is there any way to make sure the macros
do not show up in that list?

Hi Kristanna,

The following code prevents someone from pressing the ALT F8 keys, to
view/run the various subroutines If you put this sub in the workbook that
you want to protect, then if your user presses Alt+F8 or goes to
Tools|Macros|Macros... all they will get is a message box that says
disabled. Basically what this does is "hijack" the built-in ToolsMacro
Command. Works with Word etc too.

Sub ToolsMacro()
MsgBox "Disabled"
End Sub

You might also want to consider the following - it may be less intrusive to
keep the user from seeing the macros in the first place, rather than taking
away their ability to use that dialog. Although you can't grey out the Edit
button for macros that are in a workbook, you can keep them appearing in the
list altogether. A few ways to do that:
· Make them into Subs that take arguments (or Functions for that matter).
· Make them Private rather than Public Subs (only callable by other Subs in
the same Module).
· Put an "Option Private Module" declaration at the top of any code Module
housing Subs you don't want the user to see in the Macros list. These Subs
will be callable by any other sub in the same project, but not by any sub in
any other project.

Another approach would be to use an environment variable in the macro like:
If Environ("Username")<YourName Then Exit Sub
Although users could see the macro, this would effectively stop anyone
except you using it.

Cheers


"Kristanna" wrote in message
...
Hi,

I have written a spreadsheet application that has a lot of macros in it.
However they all appear in the run macro list in the tools menu. I fear that
someone may run an individual macro. Is there any way to make sure the
macros do not show up in that list?


Thanks in advance

Krissy


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 5/03/2004