AboutStuart Resnick Expertise I can answer any question relating to MS Excel formulas, or to programming with vba (Visual Basic for Applications) in the Excel environment
Experience As a consultant, I've designed Excel tools since the 90s, working for the Federal Reserve Bank, AT&T, and (currently) Gap Inc.
Expert: Stuart Resnick Date: 7/17/2008 Subject: Adding vba code that checks user identity
Question Is there a way that I can add some vba script to the following code so that it checks the identity of the user before executing? I have protected the page and allowed only a few users to be able to make entries without a password.
The following code clears contents of a column when the command button is selected:
Private Sub CommandButton1_Click()
Range("k5:k524,k531:k545,k556:k620").ClearContents
End Sub
I want to add the following directions to the above script: Check the identity of the user and if the user is allowed to make entries without a password, then execute (clear the contents). Otherwise, do not allow the contents to be cleared.
Thanks!
Answer For "identity," we'll use the Excel user name (the name that was entered by this user when the Excel application was installed on the computer).
On a hidden page named UsersAllowed, create a list in Column A of all name of people who should be able to use the macro. The lines of code below determine the Excel user name, then check if it's in Col A of UsersAllowed. If the name isn't on the list, then it ends the macro.
Dim strName As String
Dim listOfNames As Range, foundName As Range
strName = Trim(Application.UserName)
Set listOfNames = ThisWorkbook.Sheets("UsersAllowed").Columns(1)
Set foundName = listOfNames.Find(What:=strName, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
If foundName Is Nothing Then End
If you want the name of the user from the Windows login (rather than the Excel installation), then in the above code replace
