VBA Code - Hide / Unhide Sheets Macro in Protection Mode

tlc53

Active Member
Joined
Jul 26, 2018
Messages
399
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

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

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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.

Code:
Sub Beef()
    Sheets("Beef").Unprotect
    Sheets("Beef").Visible = False
    If [Q1] Then Sheets("Beef").Visible = True
    Sheets("Beef").Protect
End Sub
 
Upvote 0
Yes, I did try that and I also tried the below.

Code:
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.




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

Code:
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
 
Upvote 0
I thought I was being clever by trying this code, but that didn't work either..

Code:
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
 
Upvote 0
Ok, can you upload the worksheet to dropbox ??
 
Upvote 0
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
 
Upvote 0
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 :laugh:
Thank you so much for working it out!!
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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