Password request to run macro

[Colin Foster]

Hi,
I have a workbook with two sheets...one visible to all and one for
maintenance. I have written two macros...one to hide the maintenance sheet
(amongst other things), the other to unhide them. They are then activated by
clicking the relevant buttons that I have created on the face of the visible
sheet.
What I now need to do is password protect these buttons so that if an
unauthorised user clicks on them, XL prompts for a password.
Any suggestions?
Also, although I am building the workbook in XL XP, the solution needs to
also work in '97 and 2000 if possible due to the users requirements.
Regards
Colin Foster

 

[Patrick Molloy]

add as userform. you can set a password character in a
textbox = if the pwd is not OK then you can raise a
warning

Patrick Molloy
Microsoft Excel MVP

 

[Paul B]

Colin, if you don't mind seeing the password when you type it in then you
can use an input box like this, if you don't want to see the password you
will need to make a user form and call it with some code

Sub Password()
Dim PW As String
PW = Application.InputBox( _
Prompt:="Please Enter The Password", _
Title:="Password Required To Run This Macro", _
Type:=2)
If PW = "mypassword" Then '****password is case sensitive ******

'''*****Your Code Here*****

Else
'''****If Password Is Incorrect*********
MsgBox Prompt:="Sorry, That Is Not The Correct Password", _
Title:="Incorrect Password", _
Buttons:=vbOKOnly

End If
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

 

[Bob]

I have a method that is working very well for determining if a user can
click a button. My method is based on the logged on user. We have a
routing slip for request for parts. This has to route to each person who
does the approval. A user makes the request then clicks start routing.

There are different buttons on the Excel sheet. Approve and others. Some
are specific to certain users that are the only one allowed to click the
button. I am using a module and then reference the module checking the
UserID of the user logged on to the PC.

Module Code to get UserID:
I named this module GetUserName
-------------------------------------------
Private Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, _
nSize As Long) As Long

'use CurrentUser to get the UserID.
Public Function CurrentUser() As String
Dim strBuff As String * 512
Dim x As Long
CurrentUser = ""
x = GetUserName(strBuff, Len(strBuff) - 1)
If x > 0 Then
x = InStr(strBuff, vbNullChar)
If x > 0 Then CurrentUser = Left$(strBuff, x - 1)
End If
End Function
--------------------------------------------------------------------
Code to use to test who is logged and verify if they have permission to run
the macro.
You can use something like this or make up your own routine.
------------------
Private Sub cmdApprove_Click()

Dim cUser As String
Dim Approver as Boolean

cUser = UCase(CurrentUser) 'reads UserId referencing module
GetUserName
Approver = False 'make sure starts out false

Select Case cUser
' Case "JJones"
' approver = True
' ActiveSheet.Unprotect (mName) 'I have a routine to unprotect
and then protect changes in protected
' Range("F5").Value = Date 'cells.

Case "MHILL"
approver = True
ActiveSheet.Unprotect (mName)
Range("L5").Value = Date

Case "JSmith"
approver = True
ActiveSheet.Unprotect (mName)
Range("F7").Value = Date

Case "CJones"
approver = True
ActiveSheet.Unprotect (mName)
Range("L7").Value = Date

End Select

If approver Then 'means Approver = True
'do whatever here because the logged on user has permission.
Else 'UserID is not one of approvers.
MsgBox "Your UserID " & cUser & " is not on the Approvers
list.", vbQuestion + vbOKOnly, "UserID not Found"
End If
End Sub

----------------------------------------------------------------------------
-----
'Here is an another example of only a specific user can click the final
button:

Private Sub cmdReturnCompleted_Click()
Dim cUser As String

cUser = UCase(CurrentUser) 'change to uppercase because this seems to be
case sensitive.

If cUser = "BCLOWN" Then
'do whatever
Else
MsgBox "Only Bozo the clown can use this button."
End If

'This is working good for us and does not require password logons or
anything else. This is based on who is 'logged on to the PC as to wheither
they have access to click one of the buttons on the sheet. Of course a real
bright user could add their name to the approval list but that has never
happened to use so far.

Bob

 

[ste mac]

Hi Colin..
I have a sheet with a form that requests a password
that does not show the password (only shows asterix's_
if you want it to modify, then just let me know...

seeya ste