Locking Macros
 

I have my worksheet, workbook and VBE all with the protection turned on. Is
there a way to stop a user from going to Tool - Macro-Macros... and running
a procedure?

Locking Macros
 

Try here

http://www.rondebruin.nl/ribbon.htm

HTH
Mark

Locking Macros
 

On Fri, 1 Aug 2008 14:01:16 -0700, ranswrt
wrote:

I have my worksheet, workbook and VBE all with the protection turned on. Is
there a way to stop a user from going to Tool - Macro-Macros... and running
a procedure?

Put Private in front of Sub and it will only be accessible to other
procedures in that Module.

Private Sub DoStuff()
....
End Sub

You can also put an argument in the sub and it won't show up in the Macro
dialog.

Sub DoStuff(Optional bFake as Boolean)
....
End Sub

You don't have to do anything with bFake, just its presence will prevent it
from showing up.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Locking Macros
 

I used 'bfake' and that worked great. Is any other consequences using
'bfake' other then it not putting the macro in the dialog box?

"Dick Kusleika" wrote:

On Fri, 1 Aug 2008 14:01:16 -0700, ranswrt
wrote:

I have my worksheet, workbook and VBE all with the protection turned on. Is
there a way to stop a user from going to Tool - Macro-Macros... and running
a procedure?

Put Private in front of Sub and it will only be accessible to other
procedures in that Module.

Private Sub DoStuff()
....
End Sub

You can also put an argument in the sub and it won't show up in the Macro
dialog.

Sub DoStuff(Optional bFake as Boolean)
....
End Sub

You don't have to do anything with bFake, just its presence will prevent it
from showing up.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Locking Macros
 

I don't think so. Make sure you use the Optional keyword so it doesn't
break any existing macros that call it. Oh, and you won't be able to run it
using F5 in the VBE. You'll have to call it from the Immediate Window, but
that's a small price to pay.

On Sat, 2 Aug 2008 05:56:01 -0700, ranswrt
wrote:

I used 'bfake' and that worked great. Is any other consequences using
'bfake' other then it not putting the macro in the dialog box?

"Dick Kusleika" wrote:

On Fri, 1 Aug 2008 14:01:16 -0700, ranswrt
wrote:

I have my worksheet, workbook and VBE all with the protection turned on. Is
there a way to stop a user from going to Tool - Macro-Macros... and running
a procedure?

Put Private in front of Sub and it will only be accessible to other
procedures in that Module.

Private Sub DoStuff()
....
End Sub

You can also put an argument in the sub and it won't show up in the Macro
dialog.

Sub DoStuff(Optional bFake as Boolean)
....
End Sub

You don't have to do anything with bFake, just its presence will prevent it
from showing up.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

--
Dick

Locking Macros
 

Thanks for your help

"Dick Kusleika" wrote:

I don't think so. Make sure you use the Optional keyword so it doesn't
break any existing macros that call it. Oh, and you won't be able to run it
using F5 in the VBE. You'll have to call it from the Immediate Window, but
that's a small price to pay.

On Sat, 2 Aug 2008 05:56:01 -0700, ranswrt
wrote:

I used 'bfake' and that worked great. Is any other consequences using
'bfake' other then it not putting the macro in the dialog box?

"Dick Kusleika" wrote:

On Fri, 1 Aug 2008 14:01:16 -0700, ranswrt
wrote:

I have my worksheet, workbook and VBE all with the protection turned on. Is
there a way to stop a user from going to Tool - Macro-Macros... and running
a procedure?

Put Private in front of Sub and it will only be accessible to other
procedures in that Module.

Private Sub DoStuff()
....
End Sub

You can also put an argument in the sub and it won't show up in the Macro
dialog.

Sub DoStuff(Optional bFake as Boolean)
....
End Sub

You don't have to do anything with bFake, just its presence will prevent it
from showing up.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

--
Dick