Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent users from running macros
Hi,
I have a workbook, where the VBA code is password protected. The macros within the workbook are operated by buttons - so I'm happy with that. However, what I really want to be able to do, is to stop users from selecting Tools, Macro, and then running a macro from there (NOTE - I do not want to hide this option from the Toolbar). Is there any way of preventing users from running a macro in this way ? Note - Also, I do not want to protect the workbook either. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent users from running macros
You can hide them using private statements though it depends on you
structure. If the buttons call subs in a spearate module you will need the modul set up as: Option Private Module Public Sub Prog1 'THis sub is in a module called by a sub from a button and can only b seen by VB not users. End sub If the buttons call subs in the same module themselves: Private Sub BUtton_Click Run Prog2 End sub Private Sub Prog2 End Su -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent users from running macros
Hi Alan,
Add Option Private Module At the top of each module in the project. Alternatively, make each Sub Private. Regards, Jan Karel Pieterse Excel MVP www.jkp-ads.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent users from running macros
In addition to the other answers you've been given, procedures with
arguments and functions that return values can't be run from the Macro list, so Excel ignores them when creating the list. Therefore, simple workarounds can also include minor code modifications like the following, which should have no impact on how your existing code runs, and doesn't require changes to existing module/procedure scoping. Public Sub MySub(Optional DummyArg as Boolean) 'the argument (even when optional and never supplied) will prevent this from appearing in the macro list. End Sub or Public Function MySub()as Boolean 'the return value (which you don't have to actually use) will prevent this from appearing in the macro list. End Function -- HTH, George Nicholson Remove 'Junk' from return address. "Alan Hutchins" wrote in message ... Hi, I have a workbook, where the VBA code is password protected. The macros within the workbook are operated by buttons - so I'm happy with that. However, what I really want to be able to do, is to stop users from selecting Tools, Macro, and then running a macro from there (NOTE - I do not want to hide this option from the Toolbar). Is there any way of preventing users from running a macro in this way ? Note - Also, I do not want to protect the workbook either. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent users from running macros
But if they can find the name of the procedure, they can type the name of the
sub in the run dialog. They can't select it and run it, but they can type and run. George Nicholson wrote: In addition to the other answers you've been given, procedures with arguments and functions that return values can't be run from the Macro list, so Excel ignores them when creating the list. Therefore, simple workarounds can also include minor code modifications like the following, which should have no impact on how your existing code runs, and doesn't require changes to existing module/procedure scoping. Public Sub MySub(Optional DummyArg as Boolean) 'the argument (even when optional and never supplied) will prevent this from appearing in the macro list. End Sub or Public Function MySub()as Boolean 'the return value (which you don't have to actually use) will prevent this from appearing in the macro list. End Function -- HTH, George Nicholson Remove 'Junk' from return address. "Alan Hutchins" wrote in message ... Hi, I have a workbook, where the VBA code is password protected. The macros within the workbook are operated by buttons - so I'm happy with that. However, what I really want to be able to do, is to stop users from selecting Tools, Macro, and then running a macro from there (NOTE - I do not want to hide this option from the Toolbar). Is there any way of preventing users from running a macro in this way ? Note - Also, I do not want to protect the workbook either. -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent users from running macros
Dave:
Thanks, I didn't know that (or if I did, I had forgotten it). Up to now I've only been concerned with procedures not appearing in the list. I will have to consider whether that is sufficient. While it *probably* is, the possibility that a user a) knows about this and b) somehow learns the name of a procedure/function (even if code is protected) is definitely worth consideration. (I will just have to figure out how much consideration). -- George Nicholson Remove 'Junk' from return address. "Dave Peterson" wrote in message ... But if they can find the name of the procedure, they can type the name of the sub in the run dialog. They can't select it and run it, but they can type and run. George Nicholson wrote: In addition to the other answers you've been given, procedures with arguments and functions that return values can't be run from the Macro list, so Excel ignores them when creating the list. Therefore, simple workarounds can also include minor code modifications like the following, which should have no impact on how your existing code runs, and doesn't require changes to existing module/procedure scoping. Public Sub MySub(Optional DummyArg as Boolean) 'the argument (even when optional and never supplied) will prevent this from appearing in the macro list. End Sub or Public Function MySub()as Boolean 'the return value (which you don't have to actually use) will prevent this from appearing in the macro list. End Function -- HTH, George Nicholson Remove 'Junk' from return address. "Alan Hutchins" wrote in message ... Hi, I have a workbook, where the VBA code is password protected. The macros within the workbook are operated by buttons - so I'm happy with that. However, what I really want to be able to do, is to stop users from selecting Tools, Macro, and then running a macro from there (NOTE - I do not want to hide this option from the Toolbar). Is there any way of preventing users from running a macro in this way ? Note - Also, I do not want to protect the workbook either. -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent users from running macros
I don't worry about it.
It would be pretty amazing to guess: ResetVariablesToInitialValues <vbg George Nicholson wrote: Dave: Thanks, I didn't know that (or if I did, I had forgotten it). Up to now I've only been concerned with procedures not appearing in the list. I will have to consider whether that is sufficient. While it *probably* is, the possibility that a user a) knows about this and b) somehow learns the name of a procedure/function (even if code is protected) is definitely worth consideration. (I will just have to figure out how much consideration). -- George Nicholson Remove 'Junk' from return address. "Dave Peterson" wrote in message ... But if they can find the name of the procedure, they can type the name of the sub in the run dialog. They can't select it and run it, but they can type and run. George Nicholson wrote: In addition to the other answers you've been given, procedures with arguments and functions that return values can't be run from the Macro list, so Excel ignores them when creating the list. Therefore, simple workarounds can also include minor code modifications like the following, which should have no impact on how your existing code runs, and doesn't require changes to existing module/procedure scoping. Public Sub MySub(Optional DummyArg as Boolean) 'the argument (even when optional and never supplied) will prevent this from appearing in the macro list. End Sub or Public Function MySub()as Boolean 'the return value (which you don't have to actually use) will prevent this from appearing in the macro list. End Function -- HTH, George Nicholson Remove 'Junk' from return address. "Alan Hutchins" wrote in message ... Hi, I have a workbook, where the VBA code is password protected. The macros within the workbook are operated by buttons - so I'm happy with that. However, what I really want to be able to do, is to stop users from selecting Tools, Macro, and then running a macro from there (NOTE - I do not want to hide this option from the Toolbar). Is there any way of preventing users from running a macro in this way ? Note - Also, I do not want to protect the workbook either. -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent users from running macros
Exactly. Plus if the project uses a lot of classes, trying to run almost any
procedure without having run the initialization routines first generally won't accomplish (or damage) anything, since the necessary objects weren't created or collections are empty. Very little could be done in a cold-start vacuum. However, there are a few exceptions and they may be worth some thought. Maybe. When I have some spare time. :-) -- George Nicholson Remove 'Junk' from return address. "Dave Peterson" wrote in message ... I don't worry about it. It would be pretty amazing to guess: ResetVariablesToInitialValues <vbg George Nicholson wrote: Dave: Thanks, I didn't know that (or if I did, I had forgotten it). Up to now I've only been concerned with procedures not appearing in the list. I will have to consider whether that is sufficient. While it *probably* is, the possibility that a user a) knows about this and b) somehow learns the name of a procedure/function (even if code is protected) is definitely worth consideration. (I will just have to figure out how much consideration). -- George Nicholson Remove 'Junk' from return address. "Dave Peterson" wrote in message ... But if they can find the name of the procedure, they can type the name of the sub in the run dialog. They can't select it and run it, but they can type and run. George Nicholson wrote: In addition to the other answers you've been given, procedures with arguments and functions that return values can't be run from the Macro list, so Excel ignores them when creating the list. Therefore, simple workarounds can also include minor code modifications like the following, which should have no impact on how your existing code runs, and doesn't require changes to existing module/procedure scoping. Public Sub MySub(Optional DummyArg as Boolean) 'the argument (even when optional and never supplied) will prevent this from appearing in the macro list. End Sub or Public Function MySub()as Boolean 'the return value (which you don't have to actually use) will prevent this from appearing in the macro list. End Function -- HTH, George Nicholson Remove 'Junk' from return address. "Alan Hutchins" wrote in message ... Hi, I have a workbook, where the VBA code is password protected. The macros within the workbook are operated by buttons - so I'm happy with that. However, what I really want to be able to do, is to stop users from selecting Tools, Macro, and then running a macro from there (NOTE - I do not want to hide this option from the Toolbar). Is there any way of preventing users from running a macro in this way ? Note - Also, I do not want to protect the workbook either. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I prevent users from changing macros? | Excel Discussion (Misc queries) | |||
Prevent Users from unsharing a workbook | Excel Worksheet Functions | |||
Prevent wandering users | Excel Discussion (Misc queries) | |||
prevent users from overwriting other users data | Excel Worksheet Functions | |||
Prevent users from using AutoFilter | Excel Programming |