VBA using Active X Checkbox to hide rows on another sheet not working when the sheet is protected.

[3ddesignbros]

Hello, so I have a VBA on a dashboard of sorts sheet that contains a bunch of checkboxes that when checked unhide rows on another sheet. The code I'm using for this:

Private Sub CheckBox6_Click()
ThisWorkbook.Sheets("Filament Data Sheet").Rows("3:14").EntireRow.Hidden = Not CheckBox6
End Sub

The code above works great, with no issues. The box is checked and the rows are there. The box is unchecked and the rows are gone.

I'm now finalizing this workbook and need to protect all the sheets. However, now that the sheets are protected the checkbox cannot hide the rows in the other sheets. I've tried a ton of different codes and I can't seem to get anything to work. I'm pretty new to VBA so I'm guessing I'm missing something simple but I just can't figure it out. Below are some of the things I've tried:

Private Sub CheckBox6_Click()
ThisWorkbook.Sheets("Filament Data Sheet").Unprotect Password = "Password"
ThisWorkbook.Sheets("Filament Data Sheet").Rows("3:14").EntireRow.Hidden = Not CheckBox6
ThisWorkbook.Sheets("Filament Data Sheet").Protect Password = "password"
End Sub

The above didn't work

I tried creating the following subs:

Sub unprotect1()
ThisWorkbook.Sheets("Filament Data Sheet").unprotect "password"
End Sub

Sub protect1()
ThisWorkbook.Sheets("Filament Data Sheet").protect "password"
End Sub

Then added the protect1 and unprotect1 as follows:

Private Sub CheckBox6_Click()
unprotect1
ThisWorkbook.Sheets("Filament Data Sheet").Rows("3:14").EntireRow.Hidden = Not CheckBox6
protect1
End Sub

The above didn't work

I tried this following code:

Private Sub ChecBox6_Click()
With Sheets("Filament Data Sheet")
.Unprotect Password:="Password"
.Rows("3:14").EntireRow.Hidden = CheckBox6.Value
.Protect Password:="Password"
End With
End Sub

The above didn't work

Feels like I've tried 6 other things as well with no luck.

The best outcome I've gotten is the sheet becomes unprotected, but then I get an error hiding the rows.

Would be super appreciative of the help. Thanks

 

[Alex Blakenburg]

In your last example the Sub Name has a typo in it (ChecBox6 s/be CheckBox6)
Your subs mostly look fine. If you use the below does the message box appear ?
If it errors out, on what line and what is the error message ?
If you perform the action manually does it let you hide the rows or is there something else going on with the s/sheet preventing the hiding of rows ?

Private Sub CheckBox6_Click()
    MsgBox "Check Box Value is:  " & CheckBox6.Value & " (True = Hide Rows) "
    With Sheets("Filament Data Sheet")
        .Unprotect Password:="Password"
        .Rows("3:14").Hidden = Not CheckBox6.Value
        .Protect Password:="Password"
    End With
End Sub

 

[3ddesignbros]

Alright so I tried that code and a lot seemed to happen lol.

I first got this:

I clicked ok. After that I got this message:

I clicked ok. After that I got this message:

I clicked ok. After that, I got this message

:


I then clicked debug and it showed me the following:

At the end of all this though I checked the "Filament Data Sheet" and the sheet was unprotected and the rows that were supposed to be visible based on the checkbox were. However, the sheet never got re-protected.

Thank you for all the help already. Hopefully, all this above info helps to figure this out.

 

[3ddesignbros]

@Alex Blakenburg It seems like with all my code I've tried it obviously doesn't run until the checkbox is clicked but since it's trying to edit a protected sheet it throws the "protected sheet" message up before the code as a chance to run the "unprotect" part of the script.

Thanks for the help and look forward to your reponse.

 

[Alex Blakenburg]

I think there might be a lot more going on here.
What is the name of the sheet with the Checkbox and does it have a worksheet macro module with event macros ?
Can you copy the macros here for us to have a look at ?

Also for the sheet "Filament Data Sheet", does it have any worksheet event macros ? Please show us those as well.

I can't seem to reproduce your first error message ("The cell or chart you are trying to change ....") even with putting a chart on the sheet. Did you try doing it manually just to see if there is anything else going on with the sheet (try manually unprotecting the sheet and then try hiding those same rows) ?

 

[3ddesignbros]

So I think I got it to work...probably not the best way but it's working.

So The checkbox not only hides or unhides the rows but is also linked to a cell in a helper column that puts "True" or "False" in the cell. That helper column then populates a drop-down on a different sheet with selections based on that True or False value.

I had the drop-down unlocked so the user could select it but the helper column was locked and I think that is what was throwing the protected sheet error. I guess if the checkbox is linked to a cell that is considered a user interaction when you check the box? instead of VBA.

Because I put the following code in my start-up script.

Private Sub Workbook_Open()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Filament Data Sheet")

sh.Protect "Password" userinterfaceonly:= True

End Sub

It still threw an error though. I then unlocked the cells in the helper column and used the following code.

Private Sub Workbook_Open()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Filament Data Sheet")

sh.Protect "Password", , , , True, , , True

End Sub

The first "True" being user interface only and the second "True" being formatting rows allowed.

That code combined with unlocking the helper column but then hiding the column seems to have done the trick! Again probably not the cleanest but it's working.

 

[Alex Blakenburg]

That's great feedback. I did not know about the "userinterfaceonly" option on protect.
I have some other work around options if you are interested:

1) Click Event Error - "The cell or chart you're trying to change is on a protected sheet……."
As you pointed out this happens when you have a cell linked to the checkbox and that cell is on a protected sheet.
Your workaround to unlock the cell seems to be a good solution.
If you don't want to unlock it on "Filament Data Sheet" you could have the linked cell on the same sheet as the Checkbox and on "Filament Data Sheet" just reference that cell eg =Sheet1!A1
It would mean that the sheet with the checkbox is either not protected or have the linked cell unlocked on that sheet.
Note: The error seems to happen before the click event executes so disabling alerts or unprotecting the sheet in the click event doesn't prevent it.

2) Run time error 1004 - Unable to set hidden property
Happens when you have protection with userinterfaceonly set on the sheet and your click event tries to hide the rows on that sheet.
Your workaround of allowing row formatting also seems a good solution.
sh.Protect Password:="1234", userinterfaceonly:=True, AllowFormattingRows:=True
If you did not want to allow the user to be allowed to format rows then another option is, in the Click event, temporarily activate the protected sheet, then hide the rows.
With screenupdating disabled the user shouldn't notice it.

Private Sub CheckBox6_Click()
    Application.ScreenUpdating = False
    Worksheets("Filament Data Sheet").Activate
    Worksheets("Filament Data Sheet").Rows("3:14").EntireRow.Hidden = Not CheckBox6.Value
    Me.Activate
    Application.ScreenUpdating = True
End Sub