8

I just finished some VBA and I was wondering if there is a way to hide certain macros on Excel.

I need the user to run a certain macro and only that one, but it shows all the sub macros in Excel. I want to hide the unnecessary macros from the user so that way the user doesn't accidentally click on the wrong one.

Improve this question
2
  • 2
    so put a button on a sheet and associate the macro with it and tell the users to press the button. Commented Sep 18, 2017 at 20:56
  • Making a visible button is definitely good interfacing. You can also write some text nearby on usage advice and restrictions. Commented Sep 18, 2017 at 21:04

3 Answers 3

Reset to default
15

You can also do this by placing the macros you want to hide in a separate module and using Option Private Module at the top of the module before the code. The macros will still be available to your project but will not appear in the Macros seen by the user when he clicks the Macros button.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Worked like charm. Thank you!
@ScottCraner ah, good point! Pretty sure it's a dupe, too (but I'm too lazy to dig it up)
@Mat'sMug now it is.
5

You can either create a button in the ribbon to run the macro, or you can add "Private" before each "Sub" in the VBA editor that you don't want the user to easily access.

Improve this answer

1 Comment

... add 'Private' to all except the one you want visible to the user, of course.
3

To subjectively 'hide' certain sub procedures (i.e. 'macros') from the (Alt+F8) Developer, Macros dialog use an optional non-variant parameter that means nothing.

Sub meh(Optional w As Worksheet)
    Debug.Print "hello world"
End Sub

The meh macro will not show up in the list of macros to run. If you dim the parameter as variant it will show in the list. This is likely due to a optional variant parameter being able to use the IsMissing function. It will also not be able to be run from the VBE with F5 or stepped through with F8.

The test sub procedure will run the code correctly.

Sub test()
    meh
End Sub

Sub meh(Optional w As Worksheet)
    Debug.Print "hello world"
End Sub
Improve this answer

5 Comments

What the... so you can't even run it from the Developer screen? Interesting
Screen,...? Well no you cannot see it in the Developer, Code, Macros dialog (shortcut Alt+F8) but you can see it in the module sheet with Developer, Code, Visual Basic. That's why I stated 'subjectively' and not 'literally'. Relative instead of absolute so to speak.
@dwirony - 'course the better method is probably using a button and passwording the VBA project but if reluctant to do that then keeping it away from prying eyes using this method is an alternative.
Sorry bad terminology. From the module sheet, yes. Even with my cursor in the macro, hitting f5 doesn't run it, which I thought was strange.
Yes, it stops manual execution with both F5 and F8 in the VBE.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.