Hide macro from user in Excel but still call via button?

Question

I have a spreadsheet that will be distributed to co-workers; I want to make it as user friendly as possible so in order to run the macros I have a simple button that automates everything for them. But I don't want them to have access to running any of the macros on their own.

I've managed to somewhat do this using

Option Private Module
Public Sub Run_Batch_Report()
'The actual script that works is here, cutting it to skip to the portion that won't work'
Call Misc_Doc
MsgBox ("Report finished.")
End Sub

All that Public Sub Misc_Doc() does is import data from another spreadsheet and format it.

And then having a Command Button calling the module via application.run

Private Sub CommandButton1_Click()
Application.Run "Batching_Module.Run_Batch_Report"
End Sub

Doing this kind of works; as in it seems to run the Run_Batch_Report sub just fine, but that sub also calls other subs within the same module to complete the job. Those subs will not run UNLESS I unlock the VB in developers options for viewing and editing. Is it possible to have the sub run in its entirety (including calling other subs within the same module) or would I have to restructure my sub to just include all the other subs it calls as well?

Sorry if I worded this haphazardly - I'm actually in the middle of a meeting and juggling this while listening to the bosses.

Edit: To clarify, I've locked the VB from being viewed. When I run the script while it's still locked, it won't allow the sub to call other portions of the module. Once my password is entered to unlock VB for viewing, it works.

Answer 1

Assuming "But I don't want them to have access to running any of the macros on their own" stands for "I don't want the macros to be listed in the 'Macros' window", you already have that.

I can't reproduce your "macro doesn't run unless project is unlocked" issue, with or without Application.Run getting involved, not sure what that's about. In any case, you seem to be under the false impression that password-protecting your project gives it any kind of security. It doesn't.

VBA code is not secure. VBA project password protection is literally a joke, it only annoys the dev (you!) and prevents clueless users that wouldn't know what to do with the VBE anyway, from viewing source code that they wouldn't understand anyway - and if someone wants to see the code, trust me, they will - in a matter of seconds.

If someone can open the host document, they can get to the VBA code.

Either a user can run the macro, or they can't. If a user can click a button to invoke some VBA code, then they have permission to invoke that VBA code from anywhere.

Have your "hidden" macros in a standard module (.bas) with Option Private Module specified:

Option Private Module
Option Explicit

Public Sub SomeHiddenMacro()
    MsgBox "Hi"
End Sub

And then you can still assign that macro to some shape, by typing the macro's name (it's not going to be listed, because Option Private Module):

Click the button, see it work:

Shapes can be formatted to look much prettier than any ActiveX button:

It doesn't need to be any more complicated than that.

---

The Classy Solution

You're beginning with VBA, so I presume you haven't toyed with class modules much. One of the nice things about class modules is that their public members can't be invoked as macros, because class modules don't exist at run-time - they're types, not modules. For a type to mean anything, it needs to be instantiated - and the macro runner doesn't do that.

So put the "worker" code in a class module, say BatchReport:

Option Explicit

Public Sub Run()
    'TODO: do your thing
End Sub

Now in the macro that's attached to the button (or in the ActiveX button's Click handler), all you do is create an instance of that object with the New keyword, and invoke its Run method:

Option Private Module
Option Explicit

Public Sub RunBatchReport()
    With New BatchReport
        .Run
    End With
End Sub    

Here I'm having a With block hold the object reference. Alternatively you could have declared an object variable, and Set its reference to a New instance of the BatchReport class:

Option Private Module
Option Explicit

Public Sub RunBatchReport()
    Dim report As BatchReport
    Set report = New BatchReport
    report.Run
End Sub