Hiding macros

This can be done in a few ways, the easy way is to use Control Toolbox Buttons to Run your code. The View-Code Button-Click Event will be a "Private" Sub, private Sub's are not shown on the Macro menu!

Like:

Private Sub CommandButton1_Click()
'Run from the Sheet Module for the Sheet that has the control on it!

'Any code you want here!
MsgBox "Test1!"
End Sub

For the control toolbox button on Sheet1.

You can also do this by making the Excel Toolbar: "Tools - Macro" menu item Grayed-out and un-selectable from the menu!

Note: You must make it un-Gray on exit or it will stay that way!

Also, count the items down to "Macro" on your menu and change the Control number in the () below if needed for your system!

Private Sub Workbook_Open()
'ThisWorkbook code!

'"14" is the Excel Toolbar "Tools" menu item count down to "Macro"
'count each item including mouse-overed Gray or additional items!
Application.CommandBars("Tools").Controls(14).Enabled = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'ThisWorkbook code!

'"14" is the Excel Toolbar "Tools" menu item count down to "Macro"
'count each item including mouse-overed Gray or additional items!
Application.CommandBars("Tools").Controls(14).Enabled = True
End Sub

Thanks Joe
That is brilliant . One more question on my computer I had to change the 14 to 11 will it me 11 on all my users computers?

Thanks again
Have a great weekend
L

Its ok I check some other computers and yes they were also 11
thanks again

L

Hi,
I think it depends on which version of Excel you are using. For me (Excel 2002) Macro in Tools is 14th Control.

True it is version dependent.

It is also dependent on which menu catagory you are working with, like your list of recent files is dependent on the number you have set for this option to return.

Or, if you have the "Always Show Full Menus" option de-selected or not. In this case the count and or order can change even among the same versions.

Were it me, I'd use Joe's first suggestion of making the Sub's PRIVATE. Why monkey with enabling/disabling the user's menu? Maybe he has a generic utility macro in his personal WB that he'd like to use and now you've impacted that for him. If you feel you must disable this (will this also disable the Alt+F8 hotkey???) then I'd use the FindControl method, passing in the ID # for that menu option. I have a couple of add-ins that I've written myself that alter the content of the Tools menu. Who's to say your user(s) haven't or won't have add-ins that alter their menu structure?

Yes, Greg is correct:

Alt+F8 will still pull up the Run Macro list!

You can dissable this key combination with:

Application.OnKey "%{F8}", ""

and re-activate this key combination with:

Application.OnKey "%{F8}"


Once again as Greg has indicated, unless you take full control of the menu by first hiding any user modified toolbars and replacing them with generic default menus. You could run into problems!

I would make all my Subs "Private" and then PassWord protect the Project so my Modules are hidden!