Protecting a Macro

[jonc31]

Is it possible to have a button in an excel worksheet that is assigned to a macro that only a specific user can run but a lot of people share the workbook?

 

[stilesbn]

You could set up a user input box that asks for a password.If they enter in the wrong password have it exit sub.

e.g.

Sub pWord()

Dim userInput As String

userInput = InputBox("Please enter your password", "Password")

If Not userInput = "Password" Then
    Exit Sub
End If

'' Rest of macro here

End Sub

Anyone who reads the code would be able to figure out your password. If you wanted you could put the password on a hidden sheet and lock that sheet to everyone who doesn't have a password and reference that in the macro.

 

[Bagharmin]

Sure. You could start your code with an InputBox asking the user to enter a password. If they don't enter it (or enter it incorrectly) you could Exit Sub. Or, if you don't want to mess with an InputBox, you could always check to see if Application.UserName was equal to the UserName of the person you want to have access.

 

[Trevor G]

Try adding a command button and in the workbook open event you could add some code that will make it visible to a specific user else it hids the button

Sample code is as follows

Private Sub Workbook_Open()
If Environ("username") = "[COLOR=magenta]Trevor G[/COLOR]" Then
Sheets("[COLOR=magenta]Sheet1[/COLOR]").Shapes("[COLOR=magenta]Button 1[/COLOR]").Visible = True
Else
Sheets("[COLOR=magenta]Sheet1[/COLOR]").Shapes("[COLOR=magenta]Button 1[/COLOR]").Visible = False
End If

End Sub

 

[jonc31]

What user name does application.username use? The name the computer is set to or the actual username that is set under toos/options and then the genreal tab in excel?

 

[Bagharmin]

In Excel 2007, click the button in the upper left corner (that crazy squiggly one), then Excel Options, then look toward the bottom of the Excel Options window that appears (where it says "Personalize your copy of Microsoft Office"). The User name right below that is what you get from Application.UserName. It gives the user name specific to the copy of Excel being used.