VBA Code - Hide / Unhide Sheets Macro in Protection Mode

[tlc53]

Hi there,

I have the below macro assigned to a Check Box on sheet "G2". The Check Box is Unlocked.
I would like both sheets "Beef" and "G2" to be protected.
However, the below macro will not run if sheet "G2" is protected (it runs fine with sheet "Beef" protected and sheet "G2" unprotected).
I have tried inserting ActiveSheet.Unprotect ("") and ActiveSheet.Protect("") into the below code but that doesn't work. Does anyone know how I can get this to work with both sheets protected? Thank you

Sub Beef()
    Sheets("Beef").Visible = False
    If [Q1] Then Sheets("Beef").Visible = True
End Sub

 

[Michael M]

So this doesn't work ??
If it doesn't put a breakpoint in the code to see if it actually fires, cause I'm guessing your not referring to the check box correctly.

Sub Beef()
    Sheets("Beef").Unprotect
    Sheets("Beef").Visible = False
    If [Q1] Then Sheets("Beef").Visible = True
    Sheets("Beef").Protect
End Sub

 

[tlc53]

Yes, I did try that and I also tried the below.

Sub Beef()
    Sheets("G2").Unprotect
    Sheets("Beef").Visible = False
    If [Q1] Then Sheets("Beef").Visible = True
    Sheets("G2").Protect
End Sub

How do I insert a breakpoint? Below is the code directly before and after it, just in case this is relevant.
What confuses me, is that if G2 is unprotected it works fine so I'm not sure why the above code doesn't work. If I try running it with G2 protected, it comes back with an error saying the change is on a protected sheet.

Sub Dairy_Other()
    Sheets("Dairy").Visible = False
    If [P1] Or [P2] Or [P3] Then Sheets("Dairy").Visible = True
    If Worksheets("G2").Range("P3") = True Then
    Worksheets("Dairy").Range("132:151").EntireRow.Hidden = False
    Else
    Worksheets("Dairy").Rows("132:151").EntireRow.Hidden = True
    End If
End Sub


Sub Beef()
    Sheets("Beef").Visible = False
    If [Q1] Then Sheets("Beef").Visible = True
End Sub


Sub Sheep()
    Sheets("Sheep").Visible = False
    If [Q2] Then Sheets("Sheep").Visible = True
End Sub

 

[Michael M]

If you click in the LH border of the code window on any code line it will insert a small brown dot.
When the code is run it will stop at this point !!....until you either press F8 or run to proceed the code.
If it doesn't reach the breakpoint, it means it hasn't fired !
What sort of checkbox are you using ??
Can you upload the worksheet to dropbox ?

 

[tlc53]

Thank you! I was able to add the brown dot and when the macro was run by the check box, it turned the brown dot line yellow. I assume this means it has run to this point.
It appears to not like the Protect and Unprotect coding I am putting in, as it won't run with this coding even when G2 is unprotected. It's just a Form check box, with the macro assigned to it.
Is there another way I can get this macro to run with all sheets protected. In another spreadsheet to allow -/+ of subtotals to work in protection mode, I added the following code to "ThisWorkbook". Can this code possibly be adapted to run my hide/unhide macros in protection mode?

Sub Workbook_Open()
Dim xWs As Worksheet
Set xWs = Application.ActiveSheet
Dim xPws As String
xPws = ""
xWs.Protect Password:=xPws, Userinterfaceonly:=True
xWs.EnableOutlining = True
End Sub

 

[tlc53]

I thought I was being clever by trying this code, but that didn't work either..

Sub Beef()
    ActiveSheet.Unprotect ("")
    Sheets("Beef").Unprotect ("")
    Sheets("Beef").Visible = False
    If [Q1] Then Sheets("Beef").Visible = True
    ActiveSheet.Protect ("")
    Sheets("Beef").Protect ("")
End Sub

 

[Michael M]

Ok, can you upload the worksheet to dropbox ??

 

[tlc53]

Hi Michael,

I still can't work out why it's doing this even with adding Unprotect/Protect in the coding.

This link currently has Beef protected (no password) and sheet G2 unprotected. But as soon as G2 is protected, that's where the problems start.

https://www.dropbox.com/s/o35w2mb4o2uu5r0/G2 - Macro not working on Protection.xlsm?dl=0

Thank you.

 

[Michael M]

Try Unlocking cell Q1 and see what happens ??
If that works, you may have to apply the unlock to the other TRUE / FALSE cells as well

 

[tlc53]

I can't tell you how much I was racking my brain trying to figure this out and all along, it was because I had those cells locked
Thank you so much for working it out!!