CODEWITHSUNDEEP
vbaexcel
DaBeau96
DaBeau96

Reputation: 548

Is there a way to hide macros in Excel?

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.

Upvotes: 8

Views: 10221

Answers (3)

Robby
Robby

Reputation: 827

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.

Upvotes: 5

user4039065
user4039065

Reputation:

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

Upvotes: 3

JohnDunsmuir
JohnDunsmuir

Reputation: 166

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.

Upvotes: 15

Related Questions