Can I prevent a user from running a macro?

M

Michelle

I have a macro that I want to be able to call from other macros, but it is
important that it cannot be run by users.

I have tried making it Private, but then the other macros also can't call
it.

Is there a way around this?

Thanks

Michelle
 
R

royUK

If you place the private module in a separate module and use Optio
Private Module it will not show in the Macro list for that workbook.


Code
-------------------
Option Explicit
Option Private Module

Sub test()
MsgBox "Hello World"
End Su
-------------------


Place the main code in another module


Code
-------------------
Option Explicit

Sub main()
test
End Su
 
M

Michelle

Bloomin' marvellous answer - I can't believe I've not come across this
before - Thank you

M
 
C

Charlotte E.

Another little trick:


If you want all your macros - both the callable and those you want
'invisible' to be in the same Module (which I perfer, if the macros are
handling the same issue), don't use 'Option Private Module', but just make
an optional Dummy on the macros that you want to be invisible.

i.e.:


Public Sub MyMacro()
....code...
End sub


would become:

Public Sub MyMacro(Optional Dummy As Byte)
....code...
End sub

That way you can still call the macro, as you normally would do, but the
macro doesn't show up in the callable macros list.


CE
 
D

Dave Peterson

You could even ask for a password...

Option Explicit
Sub MichelleOnly()
dim myStr as string

myStr = inputbox(prompt:="What's the password")

if mystr <> "ThisISToPSecRet" then
exit sub
end if

'rest of code
End sub
 
Top