Prevent Users from Running Macros

[TemiU]

Other than password protecting each individual macro/ function/ procedure in my VBA code, is there any way to prevent users from running macros from their Developer tab?

I would like them to only be able to run macros from command buttons or a custom toolbar, aside from those which fire automatically.

Thanks a lot!

 

[xEXCEL LENCEx]

Basic not very protected unless you protect your macro with a password

insert the following in your macro

Application.ShowDevTools = False

 

[TemiU]

Thank you. That hides the developer tab until it is turned back on. It does not hide individual macros.

I see that it is possible to insert "Option Private Module" and "hide" the macros that way, but I need to see what the repercussions will be for each module.

 

[jtakw]

You can add an optional variable to your macro, then it won't show on the macros list accessed from the ribbon.

e.g.

Sub MyMacro(Optional x As Integer)
'Your code
End Sub

Made sure the variable you use, in my sample "x", is NOT in the body of your code; otherwise, it will interfere with your code.

And, I'm sure you probably have already, password protect your VB Project.

 

[Smitty]

Similarly, I use (Optional KeepOut As Byte).

 

[Scott Huish]

As long as the user doesn't know the name of the macro, that will work as far as a basic way to hide the macros. However, if the project is not password protected, hitting ALT-F11 will take you straight to the VB editor and the user could potentially see the names of the macros and run whatever they want.

 

[jtakw]

if the project is not password protected, hitting ALT-F11 will take you straight to the VB editor and the user could potentially see the names of the macros and run whatever they want.

That's right, that's why I mentioned:

And, I'm sure you probably have already, password protect your VB Project.

 

[TemiU]

Thanks for the fix - I will have to start implementing it in all of our macros.

Our project is, of course, password protected.

 

[TemiU]

On the same subject, the following seems to be okay, but I just wanted to verify:

I defined functions which are called from my worksheet cells (whose formulas are hidden). I would like to add "Option Private Module" to the VB code so that my clients can not see the functions on their own. That shouldn't interfere with the functionality of the cells which already have the code, right?

 

[jtakw]

Thanks for the fix - I will have to start implementing it in all of our macros.

Our project is, of course, password protected.

You're Welcome, & Merry Christmas!

On the same subject, the following seems to be okay, but I just wanted to verify:

I defined functions which are called from my worksheet cells (whose formulas are hidden). I would like to add "Option Private Module" to the VB code so that my clients can not see the functions on their own. That shouldn't interfere with the functionality of the cells which already have the code, right?

I'm not sure what you're asking is necessary, UDFs are not visible in the Macros list to begin with, and since your VB Project is password protected, they can't get to it in VBE neither.