Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password request to run macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password request to run macro
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 -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password request to run macro
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 ** "Colin Foster" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password request to run macro
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 "Paul B" wrote in message ... 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 ** "Colin Foster" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password request to run macro
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Password protection in macro ( Anybody can view my password in VB | Excel Discussion (Misc queries) | |||
how do I write a request to 'Save As' Macro | Excel Discussion (Misc queries) | |||
How to see macro code of a password protected macro without a password? | Excel Worksheet Functions | |||
How Delete Network Password Request-Worksheet in Website | Excel Worksheet Functions | |||
Request password during Protect macro | Excel Programming |