Prevent user from running private macros

Hi,

I have a workbook with several sheets and a lot of macros.

I have protected the VBA project and also the sheets and all the macros are private.

What I'm wondering is if there's a way to prevent users from unintentionally run macros
from the macro menu (Alt + F8). I know that if the macros are private they wil not show
in this menu, but if a macro is named "A" and someone type "A" in the macro meny they will be able to run it.

I need macros to be run on the right sheet only, else it can delete other codes on the sheet.

Hope someone can help me out here


Thomas

Don't believe it is possible. The only thing i can think of is have an Event Procedure to disable any macro ran.

Hi trimmjens,

There are a couple of tricks you can use:

Option Explicit

Public Sub ABC()
End Sub

Private Sub DEF()
End Sub

Public Sub GHI(idummy)
End Sub

Public Function JKL()
End Function

In the above code, only ABC shows up in the list when your type ALT F8. Private Subs are not displayed. Subs with formal parameters (i.e. idummy - even though it is not used for anything) are not displayed. Functions are not displayed. You can rename a Sub into a function, and it will work exactly the same.

Lewis

Correction:
After re-reading your original question, the only construction that may provide a solution is GHI(). You can attempt to run GHI(), but get an 'argument not optional error.

Originally Posted by trimmjens

Hi,

I need macros to be run on the right sheet only, else it can delete other codes on the sheet.

If the macros that you write must be run on a particular sheet, then specify that sheet in the code so it will only run on the good sheet. Never use ActiveSheet (unless you want the user to be able to control which sheet is effected by the macro). If some macros can be run on most sheets, but not all, code to test if the bad sheets will be effected and end the sub if they are.

1) Use code names rather than tab names.
2) Specify the heck out of your code.

If your macros are all internal and private, then no-one needs to know what they are called.

I generally prefix my subroutines with an "s" and give a descriptive name. So, I might have

Private Sub sCreateInvoice()

Now, if anyone can accidentally type that without knowing it's there, I'd be amazed. If you have a "main" subroutine that calls other subroutines and functions, you could make the name of the main routine very obtuse so that no-one can/will type it by accident, and you could set a global boolean variable to indicate that the main routine is being executed. All the other routines could check if that boolean variable is set to true and exit if it isn't.

And, has been said, if a routine has a dependency on a specific worksheet, ensure that the code relates only to that worksheet. Note that the worksheet does not have to be active/selected, just that all references are fully qualified.

Regards, TMS

To guess a macro name is almost the same as guessing a password.

Yep. And, the longer and more descriptive you make it, the less chance there is that it can be guessed or typed by accident.

Private Sub sCreate_Invoices_From_Orders_Sheet()

I know that's a bit over the top ... don't think I actually do that ... but you get the drift. No way can you guess it ... or type it accidentally.

If it's called from a main subroutine, a button or a shape, you could pass it a parameter ... useful or not.

Private Sub sCreate_Invoices_From_Orders_Sheet(ByRef OrdersSheet As Worksheet)

I believe that you can actually call a subroutine AND pass it a parameter in the Macro Dialogue Box ... but I'd be impressed if anyone did.

Nothing is bulletproof ... but you can make it pretty secure. Just don't call your subroutines A, B, C, etc., no matter how convenient.

Regards, TMS



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.