Hide macro List from Alt+F8 window.

[Shazi]

Hi Everyone,

I have one question, How to Hide Macros names from the Macro List.

If you press Alt+F78, the macro list window appears, users can run
any macro from this list. how to prevent run any macro from the Macro
List.

Regards.

Shahzad

 

[Office_Novice]

preceed the MAcro Nmae with Private i.e.

Private Sub YourMacroName()

End Sub

 

[Jim Thomlinson]

A couple of possible ways around this.

1. Add "option private module" at the top of the module. All of the
procedures in the module will now be hidden from the macro list. Note that
this method hides all procedures in the module from the macro list.

2. Change the procedure declaration to provate. Change
Sub MyStuff()
to
Private Sub MyStuff()
Not that using this method means that the scope of the procedure is changed
and it can not be called from outside of the module.

3. Add an argument to the sub. Change
Sub MyStuff()
to
Sub Mystuff(byval x as long)
Note that using this method requires you to supply a value for x any time
the sub is called.

 

[Shazi]

A couple of possible ways around this.

1. Add "option private module" at the top of the module. All of the
procedures in the module will now be hidden from the macro list. Note that
this method hides all procedures in the module from the macro list.

2. Change the procedure declaration to provate. Change
 Sub MyStuff()
 to
 Private Sub MyStuff()
Not that using this method means that the scope of the procedure is changed
and it can not be called from outside of the module.

3. Add an argument to the sub. Change
 Sub MyStuff()
 to
 Sub Mystuff(byval x as long)
Note that using this method requires you to supply a value for x any time
the sub is called.

--
HTH...

Jim Thomlinson







- Show quoted text -

Hi, Mr. Jim,

Thank you very much for providing me this detailed information, which
I dont know before. This is very useful information and its very
necessary for any vba project.

Thank you once again for your support and prompt reply....

Best Regards.

shahzad

 

[Dave Peterson]

Just to add to #3:

Sub Mystuff(Optional DummyParm as variant)

and never pass anything.

 

[Jim Thomlinson]

MyStuff Shows up in my macro list using the optional parameter. I assume it
does not on your system.

 

[Ron de Bruin]

Hi Jim

Remove optional

 

[Rick Rothstein \(MVP - VB\)]

3. Add an argument to the sub. Change

Sub MyStuff()
to
Sub Mystuff(byval x as long)
Note that using this method requires you to supply a value for x any time
the sub is called.

If you declare MyStuff as a function with no arguments, you can still call
it like a subroutine and it won't appear in the Macro listing...

Function MyStuff()
' Do your subroutine type stuff here
End Function

Rick

 

[Dave Peterson]

Nope. I just remembered incorrectly.

Sub testme(Optional parm As Variant = "")

Doesn't show up in my Tools|macro list, though.

 

[Peter T]

If you declare MyStuff as a function with no arguments, you can still call
it like a subroutine and it won't appear in the Macro listing...

Function MyStuff()
' Do your subroutine type stuff here
End Function

Rick

Ah but then it'd show up in the function list !

Regards,
Peter T

 

[Rick Rothstein \(MVP - VB\)]

3. Add an argument to the sub. Change

Ah but then it'd show up in the function list !

What is the keyboard shortcut for displaying that list?

Rick

 

[Peter T]

If you declare MyStuff as a function with no arguments, you can

What is the keyboard shortcut for displaying that list?

Rick

Shift F3, look under 'All' or 'User Defined'
or
via the Insert menu
Alt i, f

Regards,
Peter T

 

[Rick Rothstein \(MVP - VB\)]

If you declare MyStuff as a function with no arguments, you can

Shift F3, look under 'All' or 'User Defined'
or
via the Insert menu
Alt i, f

Oh, that list... I thought you were referring to something else. I think a
function will show up in that list only if it is placed in a Module. Since
we were talking about macros, I assumed they were being placed in the
worksheet's code window, not a Module. My proposal to use a function assumed
the function would take the place of the subroutine (macro) and be placed in
the worksheet's code window as well. From there, I don't believe it would
show up in any lists.

Rick

 

[Wigi]

Or declare the Sub as Private.

 

[Peter T]

Oh, that list... I thought you were referring to something else. I think a
function will show up in that list only if it is placed in a Module. Since
we were talking about macros, I assumed they were being placed in the
worksheet's code window, not a Module. My proposal to use a function assumed
the function would take the place of the subroutine (macro) and be placed in
the worksheet's code window as well. From there, I don't believe it would
show up in any lists.

Rick

The OP was talking about macros that show up in the Macro list, Alt F8. Only
macros (Subs) in "normal" modules appear in this list, unless they are
prevented from doing so by use of Private (module or sub) or with a dummy
argument as suggested by others.

Of course one can similarly make the Function private to prevent them
appearing in the list (I do that with all functions that are not intended as
UDF's).

Regards,
Peter T

 

[Rick Rothstein \(MVP - VB\)]

The OP was talking about macros that show up in the Macro list, Alt F8.
Only
macros (Subs) in "normal" modules appear in this list, unless they are
prevented from doing so by use of Private (module or sub) or with a dummy
argument as suggested by others.

"Normal" modules? On my system (XL2003), without the Private designation,
the name of a Sub (without any arguments) shows up in the Macro List
(Alt+F8) whether it is in a VB Module (click Insert/Module from the VBE menu
bar) or in what I call a "worksheet code window" (right click the worksheet
tab on a worksheet, then select View Code from the popup menu; or double
click the sheet name in the project list inside the VB editor)... are these
what you mean by "'normal' modules" (if so, what would a non-normal module
be)? On the other hand, a Function (again, without the Private designation)
only shows in the Function List (Shift-F3) if it is in a VB Module.

Rick

 

[Peter T]

"Normal" modules? On my system (XL2003), without the Private designation,
the name of a Sub (without any arguments) shows up in the Macro List
(Alt+F8) whether it is in a VB Module (click Insert/Module from the VBE menu
bar) or in what I call a "worksheet code window" (right click the worksheet
tab on a worksheet, then select View Code from the popup menu; or double
click the sheet name in the project list inside the VB editor)... are these
what you mean by "'normal' modules" (if so, what would a non-normal module
be)? On the other hand, a Function (again, without the Private designation)
only shows in the Function List (Shift-F3) if it is in a VB Module.

Rick

Yes you are right, a Public sub in an object module appears in the macro
prefixed with its object name like this
sheetCodeName.MyMacro ' not necessarily tab name
ThisWorkbook.MyMacro

Perhaps I should not be so surprised as it does make sense. However, really,
I don't believe I have ever seen a macro intended to appear in the macro
list written in anything other than a "Normal" module!

Regards,
Peter T