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.

 

[mse330]

Can you try this & see if it helps ...

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

 

[morrisps]

There are no breakpoints

 

[morrisps]

I tried the code that you posted, but it did not work. It gave the same error, "The cell or chart you're trying to change is on a protected sheet. To make a change, unportect the sheet. You might be requested to enter a password."

I have tried all sorts of protection combinations and code to unprotect all the sheets. The trouble is that for this particular paragraph of code it only works if I manually unprotect the "Info & Readings" sheet first. None of my code to unprotect that sheet appears to run. The code runs perfectly if the sheet is unprotected. The line to reprotect the sheet works perfectly too.

I think it has something to do with the ranges or the selection. Because even if I take out the hide sheet operation it still fails to unprotect first.

 

[mse330]

That's strange ... So if you have only 1 line of code in your macro which is Sheets("Info & Readings").Unprotect Password:="ex03740" ... Would that work ?

 

[xenou]

Stupid question but which sheet is ActiveSheet in your code.
Also, you do want to use breakpoints to test particular lines of code.


Using breakpoints and other essential debugging techniques:
http://krgreenlee.blogspot.com/2006/04/programming-excel-vba-debugging-for.html

 

[morrisps]

That's strange ... So if you have only 1 line of code in your macro which is Sheets("Info & Readings").Unprotect Password:="ex03740" ... Would that work ?

That one line doesn't work either. It is like it skips over that line of code some how. I tried it with a break point right after the unprotect and it still failed.

The active sheet is "Info & Readings".

 

[morrisps]

So I still don't understand why the problem was occurring, my assumption is that it had something to do with the way cell U42 was structured.

By reformatting the sheet slightly and using the following two operations I was able to fix the problem.

[/COLOR]Sub Password()

Dim ws As Worksheet

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

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$W$42" Then
    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
    End If
End Sub

Thanks for your assistance.

 

[mse330]

So I still don't understand why the problem was occurring, my assumption is that it had something to do with the way cell U42 was structured.

By reformatting the sheet slightly and using the following two operations I was able to fix the problem.

[/COLOR]Sub Password()

Dim ws As Worksheet

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

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$W$42" Then
    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
    End If
End Sub

Thanks for your assistance.

You are welcome ... Glad this is working out for you