Disable Ability To Run Macros 2

[Romary]

I have macros that lock down and unlock the contents of my spreadsheet. I don't want my users to be able to run these macros. How would I remove the ability to run macros from the spreadsheet? Ideally, I would like to remove the macro option from the toolbar.

 

[Loomah]

Hi
To disable the macro menu use

CommandBars("Macro").Enabled = False

But this will still leave you open to users going ALT+F8. You can get round this by making your Subs Private
ie Private Sub UnProtect()

For other options in removing functionality from cammandbars etc have a look in the FAQ section for one of my FAQs on how to disable commandbars etc (can't remember the number!)

Happy Friday

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[TonyJollans]

Hi Romary,

As far as Excel is concerned, when a macro runs it is the user running it. It doesn't make any distinction between code the user explicitly runs and code which the user implicitly allows to run. Consequently, subject ultimately to user control, macros in a workbook can either run or not run.

That said, this will delete the Macro option from the Tools menu if you want, although it will not stop users accessing and running macros if they know how.

[blue]Application.CommandBars("Tools").FindControl(ID:=30017).Delete[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Romary]

TonyJollans & Loomah,
Thank you for the valuable advice. Both options worked.

 

[Romary]

TonyJollans,
After running this I don't have the Macros on the toolbar:
Application.CommandBars("Tools").FindControl(ID:=30017).Delete

How do I unDelete it?

 

[TonyJollans]

Hi Romary,

You didn't ask that

The easiest way is just to reset the Tools menu ..

[blue]Application.CommandBars("Tools").Reset[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.