Can I prevent a user from running a macro?
 

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

Can I prevent a user from running a macro?
 

If you place the private module in a separate module and use Option
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 Sub
--------------------


Place the main code in another module


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

Sub main()
test
End Sub
--------------------


--
royUK

Hope that helps, RoyUK
For tips & examples visit 'my web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35285

Can I prevent a user from running a macro?
 

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

M

"royUK" wrote in message
...

If you place the private module in a separate module and use Option
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 Sub
--------------------


Place the main code in another module


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

Sub main()
test
End Sub
--------------------


--
royUK

Hope that helps, RoyUK
For tips & examples visit 'my web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35285

Can I prevent a user from running a macro?
 

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





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

M

"royUK" wrote in message
...

If you place the private module in a separate module and use Option
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 Sub
--------------------


Place the main code in another module


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

Sub main()
test
End Sub
--------------------


--
royUK

Hope that helps, RoyUK
For tips & examples visit 'my web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile:
http://www.thecodecage.com/forumz/member.php?userid=15 View this
thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35285

Can I prevent a user from running a macro?
 

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

Michelle wrote:

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

--

Dave Peterson