My ".unprotect" code runs fine on one piece of code and fails on the other and I cannot figure out why

[morrisps]

I am working on a spreadsheet to track maintenance, it needs to be protected so that none of the formulas or formats are messed up by the users.

Anyway I have this piece of Code that changes the sheet based on a a choice earlier on, and it runs perfectly when the sheet is protected or not:

 If Target.Address = "$K$22" Then    'hides or unhides the appropriate columns for phase 1 or 3 power.    ActiveSheet.Unprotect Password:="ex03740"
    If Target.Value = "1" Then
            Range("A25", "A27").EntireRow.Hidden = False
            Range("A34", "A36").EntireRow.Hidden = False
            Range("A28", "A32").EntireRow.Hidden = True
            Range("A37", "A41").EntireRow.Hidden = True
        Else
            Range("A25", "A27").EntireRow.Hidden = True
            Range("A34", "A36").EntireRow.Hidden = True
            Range("A28", "A32").EntireRow.Hidden = False
            Range("A37", "A41").EntireRow.Hidden = False
        End If
        ActiveSheet.Protect Password:="ex03740"
    End If

And then I have this piece of code:

If Target.Address = "$U$42" Then    'hides or unhides the rows for battery dischrage test
    ActiveSheet.Unprotect Password:="ex03740"
    If Target.Value = "Yes" Then
            ActiveSheet.Range("A48", "A50").EntireRow.Hidden = False
            Sheets("Discharge Test").Visible = True
        Else
            ActiveSheet.Range("A48", "A50").EntireRow.Hidden = True
            Sheets("Discharge Test").Visible = False
        End If
        ActiveSheet.Protect Password:="ex03740"
    End If

which is especially the same, but tells me the change is trying to be made on a protected sheet even though I have the unprotect line there.

Now I do realize that I have alterations to another sheet in that piece of code. However, I have tested it with the other sheet protected and unprotected and it makes no difference. I get the same error: "The cell or chart you're trying to change is on a protected sheet. To make a change, unprotect the sheet. You might be requested to enter a password."

Any help would be much appreciated.

 

[morrisps]

One more thing, when I run the second piece of code, the one that fails, when the sheet is unprotected it works perfectly and even protects the sheet when it is done. It then fails on further changes because the sheet is protected.

 

[xenou]

One more thing, when I run the second piece of code, the one that fails, when the sheet is unprotected it works perfectly and even protects the sheet when it is done. It then fails on further changes because the sheet is protected.

Hi,
when you say "it then fails on further changes" it just sounds like you need to unprotect in other places ... so you probably need to check all the places in your code where changes are being made.

 

[mse330]

If you are using the same password for all sheets, then I suggest that you use "UserInterFaceOnly" option when protecting through VBA so you don't have to worry about unprotect & protect the sheet every time you run a macro ... Unprotect all sheets then use the below code to protect the sheets again but using "UserInterFaceOnly" this time so you don't need to unprotect the sheet to alter the sheet through VBA

Private Sub Password()

Dim ws As Worksheet

For Each ws In Worksheets
    ws.Protect Password:="ex03740", UserInterFaceOnly:=True
Next ws

End Sub

 

[morrisps]

The code runs every time cell U42 is changed. What I meant is that if the sheet containing cell U42 is unprotected the code will run once, and then protect itself at the end of the code and fail to run a second time. It is like it is skipping over the unprotect code.

 

[morrisps]

mse330, thanks for the suggestion, but it still doesn't work. For whatever reasons this piece of code:

If Target.Address = "$U$42" Then    'hides or unhides the rows for battery dischrage test
    ActiveSheet.Unprotect Password:="ex03740"
    If Target.Value = "Yes" Then
            Sheets("Info & Readings").Range("A48", "A50").EntireRow.Hidden = False
            Sheets("Discharge Test").Visible = True
        Else
            Sheets("Info & Readings").Range("A48", "A50").EntireRow.Hidden = True
            Sheets("Discharge Test").Visible = False
        End If
        ActiveSheet.Protect Password:="ex03740"
    End If

only runs when the "Info & Readings" Sheet is unprotected. It seems to make no difference to how the code runs whether or not "Discharge Test" is protected. When "Info & Readings" is unprotected the code will run if "Discharge Test" is protected.

 

[xenou]

What do you mean "it fails to run a second time"? Fails how? What is the error?

 

[mse330]

mse330, thanks for the suggestion, but it still doesn't work. For whatever reasons this piece of code:

If Target.Address = "$U$42" Then    'hides or unhides the rows for battery dischrage test
    ActiveSheet.Unprotect Password:="ex03740"
    If Target.Value = "Yes" Then
            Sheets("Info & Readings").Range("A48", "A50").EntireRow.Hidden = False
            Sheets("Discharge Test").Visible = True
        Else
            Sheets("Info & Readings").Range("A48", "A50").EntireRow.Hidden = True
            Sheets("Discharge Test").Visible = False
        End If
        ActiveSheet.Protect Password:="ex03740"
    End If

only runs when the "Info & Readings" Sheet is unprotected. It seems to make no difference to how the code runs whether or not "Discharge Test" is protected. When "Info & Readings" is unprotected the code will run if "Discharge Test" is protected.

Why don't just add another line to unprotect the other sheet (or all sheets) when a change is triggered then re-protect all sheets again ?

 

[morrisps]

Why don't just add another line to unprotect the other sheet (or all sheets) when a change is triggered then re-protect all sheets again ?

The issue isn't with the protection, it is that it is not running the unprotect line of code. for some reason it is skipping over it. And I cannot figure out why it is skipping over it.

 

[xenou]

Have you set a breakpoint on line one of this code?