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 have this so far:

Private Sub Workbook_Open()
Dim S As String
Const SheetPass = "protect"
Const Pass1 = "test"
Const Pass2 = "this"
S = InputBox("Please input your password.", "Password Input")
If S <> vbNullString Then
If S = Pass1 Then
Sheets("Sheet1").Range("C8").Locked = False
Sheets("Sheet1").Range("F8").Locked = True
ElseIf S = Pass2 Then
Sheets("Sheet1").Range("F8").Locked = False
Sheets("Sheet1").Range("C8").Locked = True
End If
End If
End Sub

Now I would like to add the following sub to it in order to shut down the other cell i.e. if I want C8 make F8 off limits and vice versa:

Private Sub WorkSheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Target.Address = [A1].Address Then
MsgBox "Don't touch me!"
Application.Undo
End If
End Sub

I realize the second is for a particular worksheet, but I would like to add it in the workbook level in order to stop in at the beginning.

Thanks a lot Tommy Gun for your help thus far.

David

 

[TommyGun]

Why wouldn't you use the Worksheet_Activate/Deactivate combination that was sent in the sample? How does this not work for you?

 

[dgrimm68]

I decided that I wanted to have the security on the workbook level instead of the worksheet level. I converted the activate and deactivate over from worksheet to workbook and it works great except for not shuting down the second cell. Hopefully that makes sense.

 

[TommyGun]

Okay well here's the code you requested on the Workbook level. Not sure what you want from it though...

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetChange(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Excel.Range)

<SPAN style="color:#00007F">If</SPAN> Sh.Name = "Sheet1" <SPAN style="color:#00007F">Then</SPAN>

    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
    
    <SPAN style="color:#00007F">If</SPAN> Target.Address = [A1].Address <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "Don<SPAN style="color:#007F00">'t touch me!"</SPAN>
        Application.Undo
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

    Application.EnableEvents = <SPAN style="color:#00007F">True</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>
</FONT>

 

[dgrimm68]

Thank you Tommy Gun.

My CFO is very particular that is why I moved it up to the book level from the sheet level.

Again thank you.

 

[dgrimm68]

Hopefully this is the last time. I have the first two parts working, its just this third part that I am having trouble with. I need for the two cells C8 and F8 to be locked if both have true, with a statement to pop up also.

ElseIf Sheets("Sheet1").Range("C8").Locked = True And Sheets("Sheet1").Range("F8").Locked = True Then
If Sh.Name = "Sheet1" Then
Application.EnableEvents = False
If Target.Address = [C8].Address Then
MsgBox "You don't have the authority to change the data."
Application.Undo
If Target.Address = [F8].Address Then
MsgBox "You don't have the authority to change the data."
Application.Undo
End If
End If
Application.EnableEvents = True
End If
End If
End Sub


Thank you in advance.

 

[TommyGun]

Maybe this is what you are looking for...

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetChange(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Excel.Range)

<SPAN style="color:#00007F">If</SPAN> Sh.Name = "Sheet1" <SPAN style="color:#00007F">Then</SPAN>

    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>

    <SPAN style="color:#00007F">With</SPAN> Sh
    
        <SPAN style="color:#00007F">If</SPAN> Target.Address = .[A1].Address <SPAN style="color:#00007F">Then</SPAN>
            MsgBox "Don<SPAN style="color:#007F00">'t touch me!"</SPAN>
            Application.Undo
        <SPAN style="color:#00007F">ElseIf</SPAN> (.[C8].Locked And .[F8].Locked) <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Address
                <SPAN style="color:#00007F">Case</SPAN> .[C8].Address, .[F8].Address
                    MsgBox "You don<SPAN style="color:#007F00">'t have authority to change the data."</SPAN>
                    Application.Undo
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

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

    Application.EnableEvents = <SPAN style="color:#00007F">True</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>
</FONT>

 

[dgrimm68]

Here is the complete sub, I couldn't get the sub that you gave me Tommy to work within my sub by removing the sub line. So here it is so you can see it all.

Private Sub WorkBook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
If Sheets("Sheet1").Range("C8").Locked = False And Sheets("Sheet1").Range("F8").Locked = True Then
If Sh.Name = "Sheet1" Then
Application.EnableEvents = False
If Target.Address = [F8].Address Then
MsgBox "You don't have the authority to change the data."
Application.Undo
End If
Application.EnableEvents = True
End If
ElseIf Sheets("Sheet1").Range("F8").Locked = False And Sheets("Sheet1").Range("C8").Locked = True Then
If Sh.Name = "Sheet1" Then
Application.EnableEvents = False
If Target.Address = [C8].Address Then
MsgBox "You don't have the authority to change the data."
Application.Undo
End If
Application.EnableEvents = True
End If
ElseIf Sheets("Sheet1").Range("C8").Locked = True And Sheets("Sheet1").Range("F8").Locked = True Then
If Sh.Name = "Sheet1" Then
Application.EnableEvents = False
If Target.Address = [C8].Address Then
MsgBox "You don't have the authority to change the data."
Application.Undo
If Target.Address = [F8].Address Then
MsgBox "You don't have the authority to change the data."
Application.Undo
End If
End If
Application.EnableEvents = True
End If
End If
End Sub

Sorry for being so much trouble Tommy Gun. ray:

 

[TommyGun]

Ok, replace your entire code with this...

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetChange(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Excel.Range)

<SPAN style="color:#00007F">If</SPAN> Sh.Name = "Sheet1" <SPAN style="color:#00007F">Then</SPAN>

    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>

    <SPAN style="color:#00007F">With</SPAN> Sh

        <SPAN style="color:#00007F">If</SPAN> (<SPAN style="color:#00007F">Not</SPAN> .[C8].Locked And .[F8].Locked) <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">If</SPAN> Target.Address = .[F8].Address <SPAN style="color:#00007F">Then</SPAN>
                MsgBox "You don<SPAN style="color:#007F00">'t have the authority to change the data."</SPAN>
                Application.Undo
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">ElseIf</SPAN> (.[C8].Locked And <SPAN style="color:#00007F">Not</SPAN> .[F8].Locked) <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">If</SPAN> Target.Address = .[C8].Address <SPAN style="color:#00007F">Then</SPAN>
                MsgBox "You don<SPAN style="color:#007F00">'t have the authority to change the data."</SPAN>
                Application.Undo
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">ElseIf</SPAN> (.[C8].Locked And .[F8].Locked) <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Address
                <SPAN style="color:#00007F">Case</SPAN> .[C8].Address, .[F8].Address
                    MsgBox "You don<SPAN style="color:#007F00">'t have authority to change the data."</SPAN>
                    Application.Undo
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

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

    Application.EnableEvents = <SPAN style="color:#00007F">True</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></FONT>

 

[dgrimm68]

Thank you Tommy Gun

That worked great.