Prevent Users from Running Macros

[TemiU]

Other than password protecting each individual macro/ function/ procedure in my VBA code, is there any way to prevent users from running macros from their Developer tab?

I would like them to only be able to run macros from command buttons or a custom toolbar, aside from those which fire automatically.

Thanks a lot!

 

[mikerickson]

Easier than an optional argument, you could use Option Private Module to hide the names of all the macros in a module.

 

[jtakw]

.

 

[GTO]

On the same subject, the following seems to be okay, but I just wanted to verify:

I defined functions which are called from my worksheet cells (whose formulas are hidden). I would like to add "Option Private Module" to the VB code so that my clients can not see the functions on their own. That shouldn't interfere with the functionality of the cells which already have the code, right?

I just wrote a couple of quick/easy UDFs and a Sub and tested. The UDF works fine with Option Private Module.

It sounds to me like you already have the sheet protected and the UDFs in places that you want.

If that is the case, while I agree with jtakw that Function procedures do not show up in the Macro Run Dialog anyways, having the Option Private Module in place (at least for me in Excel 2010) prevents the function from appearing in the little drop-down list that appears after you type in the equals sign. I mention in case this is advantage in keeping the UDF a tiny bit more "hidden" and as Mike mentioned, it's an awfully easy way of dropping Sub procedures out of the Macro Dialog.

As I did not see it mentioned:

If the user knows the name of a Function and any required args, as mentioned, the UDF can still be entered onto a sheet. At least for me (Excel 2010 for WIN), this includes Functions marked Private.

Further, in the Macro Dialog, you can enter the name of either a Sub or Function procedure (marked Private and/or in a Private Module) and as long as there are no required parameters, it'll run.

Actually, once you correctly enter the name of a Private Sub or Private Function into the Macro Dialog, the 'Run' button becomes enabled (Regardless of whether any arguments are required). If the VBProject is not protected, so does the 'Delete' button. 'Run' runs the code, or if there are missing args, returns the error. 'Delete' works regardless of missing arguments.

Once I password protected with view locked the project (and closing and reopening the wb of course), the only difference I noted was in that using the Macro Dialog, the 'Delete' button stays disabled.

Here's the cheesy code I used, in a Standard Module:

Option Explicit
Option Private Module
  
Private Sub TestMacroDialogWithSub()
  MsgBox "Hello world! (from TestMacroDialogWithSub)", 0&, vbNullString
End Sub
  
Private Function TestMacroDialogWithFunction(Optional ByVal s As String)
  Application.Volatile
  MsgBox "Greetings from TestMacroDialogWithFunction!" & _
         IIf(Not (s = vbNullString), Space(2) & s, ""), _
            0&, vbNullString
End Function
  
Private Function TestMacroDialogWithFunctionAndRequiredArg(ByVal Cell As Range)
  Application.Volatile
  MsgBox Cell.Value & " is the current value of " & Cell.Address(0, 0, 1)
End Function
  
Private Function MyFunction(ByVal CellRange As Range) As Double
Dim Cell      As Range
Dim dblVal    As Double
Dim dblSubTot As Double
  
  For Each Cell In CellRange.Cells
    If IsNumeric(Cell.Value) Then
      dblVal = Val(Cell.Value)
      dblSubTot = dblSubTot + dblVal
    End If
  Next
  
  MyFunction = dblSubTot
    
End Function

Merry Christmas to all,

Mark

 

[GTO]

ACK! Here's the sheet.

Excel Workbook
	A	B	C	D
1	VBProject Password:	1234		3
2				4
3		23		3
4		0		6
5		0		4
6				3
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B3	=myfunction(D1:D6)
B4	=TestMacroDialogWithFunction("Merry Christmas.")
B5	=TestMacroDialogWithFunctionAndRequiredArg(B3)

Mark

 

[TemiU]

Thank you so much everyone for all of your help!

Mark - thanks for going to the trouble to write and test code.

Can you answer some questions for me:
1. Can you clarify why I need Option Explicit?
2. What is Application.Volatile?
3. I'm fairly certain this works, but wanted to double check - if a module is private, I can still use a global variable that spans the whole program, correct?

Thanks again!
Temi

 

[GTO]

...Mark - thanks for going to the trouble to write and test code. ...

Happy to do it. I did not recall seeing MikeRickson's point about using Private Module before, so it was a nice thing to learn (or re-learn if a blond-moment, of which I have a lot...).

1. Can you clarify why I need Option Explicit?

Check out the Help topic, but basically, Option Explicit in place enforces or requires you to declare all variables. It's not required, but IMO, it's just too good of a thing to not use. In short, it will save you headaches later. I type slow, so just one example: If you declare (which you always should) a variable, and let us say you make it 'MyDouble' and declared it as a Double; when you later accidently type 'MyyDouble = .000232' ... without Option Explicit, you have just implicitly declared a new Variant variable with the value of .000232. Later in the program's execution, when you go to use the value of 'MyDouble', things will go to [bleep]. Does that make sense?

=What is Application.Volatile?

Straight from Help: "Marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it's not inside a user-defined function used to calculate a worksheet cell."

In this case, nothing to do with logic. Rather, I simply wanted to force the UDF's to 'fire' whenever possible; because it's example code.

3. I'm fairly certain this works, but wanted to double check - if a module is private, I can still use a global variable that spans the whole program, correct?

Yes. Mostly, Private Module is to block access from other projects running at the same time.

Hope that helps,

Mark

 

[TemiU]

That helps a lot!

I always declare my variables, so I had totally forgotten that there was an option not to!

The Application.Volatile may come in handy - I had not known that it was an option.

Thanks again,

Temi

 

[GTO]

Hi Temi,

You are most welcome of course and Happy Coding!

Mark