Password protection

[dgrimm68]

I am trying to create a vba that will enable me to have one user enter a password and work in a particular cell and have a second user enter a password to edit a second cell. Any ideas. [/code]

 

[dgrimm68]

I will expand on my question. I would like for the sheet to be protected until a password is entered by a particular person to edit a cell and then reprotect the cell once they get out of the worksheet. I would need this for two different persons with different passwords.

Thank you

 

[TommyGun]

Right-Click on the Sheet tab that you want to protect, and select view code. Delete the code that is placed by default and insert this. Change to your needs....

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Activate()

<SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>

<SPAN style="color:#00007F">Const</SPAN> SheetPass = "protect"
<SPAN style="color:#00007F">Const</SPAN> Pass1 = "test"
<SPAN style="color:#00007F">Const</SPAN> Pass2 = "this"

Me.Protect Password:=SheetPass, DrawingObjects:=True, Contents:=True, _
    Scenarios:=True, UserInterfaceOnly:=<SPAN style="color:#00007F">True</SPAN>

s = InputBox("Please input your password.", "Password Input")

<SPAN style="color:#00007F">If</SPAN> s <> vbNullString <SPAN style="color:#00007F">Then</SPAN>

    <SPAN style="color:#00007F">If</SPAN> s = Pass1 <SPAN style="color:#00007F">Then</SPAN>
        Me.[A1].Locked = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">ElseIf</SPAN> s = Pass2 <SPAN style="color:#00007F">Then</SPAN>
        Me.[A2].Locked = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Deactivate()

Me.[A1].Locked = <SPAN style="color:#00007F">True</SPAN>
Me.[A2].Locked = <SPAN style="color:#00007F">True</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[Derek]

Hi there

Another alternative, (not as sophisticated), which only allows a value change in a specific cell. Works on the doubleclick event macro (after pasting into the vb editor for the sheet, the password prompt is activated by doubleclicking any cell.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
answer = InputBox("Enter Password", "PASSWORD PROTECTED")
If answer = "password1" Then
ActiveSheet.Unprotect "password3"
[G4].Value = InputBox("enter new value for cell G4", "CHANGE VALUE IN G4")
ActiveSheet.Protect "password3"
Else
If answer = "password2" Then
ActiveSheet.Unprotect "password3"
[G4].Value = InputBox("enter new value for cell G4", "CHANGE VALUE IN G4")
ActiveSheet.Protect "password3"
Else
ActiveSheet.Protect "password3"
Exit Sub
End If
End If
End Sub


regards
Derek

 

[dgrimm68]

Tommygun - which codes are you referring to when you say to delete the default codes. I could not get the code to work for me yet.

Derek - Where do you set the passwords to get into the cell. I tried your code out and anything that I typed in for the password seemed to work?

Thank you to both....

ray:

 

[TommyGun]

The code should go on the Worksheet object (ie Sheet1). How is the code not working???

 

[dgrimm68]

I am not getting a password prompt for cells a1 or a2 and if I am understanding the code correctly there is not an input box coming up either. I am free to type anything in the two cells.

 

[TommyGun]

Where did you place the code?

 

[dgrimm68]

I placed the code at the beginning of sheet 1 codes.

 

[TommyGun]

Okay, I will email you a sample.