How to disable macros?

  • Thread starter Thread starter LenB
  • Start date Start date
L

LenB

Greetings,

I would like to share a spreadsheet with another person,
but I would like the other person to not have access to my
macros and would like to disable them or make
them "invisible" to the other person. Is this possible?

Thanks,

Len
 
Hi LenB

You can place Private before a sub like this
Private Sub test()

Or add this at the top of the module
Option Private Module

This way you don't see it in the macro list (Alt-F8)


You can Protect your project in the VBA editor

Alt-F11
In the menubar Tools>VBA project properties
See the protection tab

This way they can't see or edit your code
 
Len,

If you don't need them to see or use them, why not just Remove/Export
them. You can then Import them back in after you send the other person
the spreadsheet without the macros.

Other options:
At the very top of each of your modules, use:
Option Private Module
The above will hide the macros from the Tools/Macro/Macros list

From the VBA Editor:
Tools/VBAProject Properties
Protection Tab
Lock Project for viewing
Enter passwords

The above will prevent someone from viewing the code in the VBA Editor
without the proper password.

John
 
Thanks for the replies. Is there anyway to disable the
macros? My spreadsheet has icons on them that have macros
assigned to them. I would like to inactivate the macros
(or icons) when I send the worksheet to another third
party.
 
Len,

If a button is assigned to a macro and macros are enabled in Excel
then no, you can't do it.
Best bet might be my first suggestion.
Export and remove the code.
Save the workbook and send it to the other person (none of the buttons
will work because there is no code in the workbook).
Then import the module(s) back into the workbook and resave it.

John
 
Hi Len

Make sure the macros knows it's you that runs them, if not then exit before doing stuff.
Either check the logon username with some API, and then something like

If StrUser <> "LenB" then Exit sub

or check if one of your own computer's files is present

If Dir("C:\Hunks\Arnie.mov") = "" then exit sub

or something like that, use your imagination. Pretty useful and simple, but requires
protected code so no one can read your criteria.
 
Back
Top