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

morrisps

New Member
Joined
Jul 17, 2017
Messages
15
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:
Code:
 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:
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.
 
Can you try this & see if it helps ...

Code:
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
 
Upvote 0

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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.
 
Upvote 0
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 ?
 
Upvote 0
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".
 
Upvote 0
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.

Code:
[/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.
 
Last edited:
Upvote 0
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.

Code:
[/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 :)
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,199
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top