"Unable to set the hidden property of the range class"

[caet_]

Hello,

I hope to find you well!

I need to protect some fields of an Excel sheet, that contains the following VBA code. However, once I protect, this error is always appearing: Unable to set the hidden property of the range class.

Private Sub Worksheet_Change(ByVal Target As Range)



With Sheets("Sheet1")



If Sheet1.[H20] = "PRODUCT A" Then

.CheckBoxes("Check Box 5").Visible = False

.CheckBoxes("Check Box 6").Visible = False

.CheckBoxes("Check Box 1").Visible = True

.CheckBoxes("Check Box 2").Visible = True

.Rows("21:22").EntireRow.Hidden = True

End If



If Sheet1.[H20] = "PRODUTCT B" Then

.CheckBoxes("Check Box 5").Visible = True

.CheckBoxes("Check Box 6").Visible = True

.CheckBoxes("Check Box 1").Visible = True

.CheckBoxes("Check Box 2").Visible = True
.Rows("21:22").EntireRow.Hidden = False

End If



If Sheet1.[D10] = 1 Or Sheet1.[D10] = 2 Then



.CheckBoxes("Check Box 7").Visible =False

.CheckBoxes("Check Box 8").Visible =False


.Rows("33:34").EntireRow.Hidden = False

End If



If Sheet1.[D10] = 3 Or Sheet1.[D10] = 4 Then


.CheckBoxes("Check Box 9").Visible = True

.CheckBoxes("Check Box 10").Visible = True

.Rows("33:34").EntireRow.Hidden = True

.Rows("31:32").EntireRow.Hidden = False

End If




End With



End Sub

Any ideas?

Thank you very much.

 

[mumps]

Try this version of the macro. Please note that the macro unprotects the sheet at the beginning and then protects it again at the end. Change the protection password (in red) to suit your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D10,H20")) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect Password:="MyPassword"
    Application.ScreenUpdating = False
    With Sheets("Sheet1")
        Select Case Target.Address
            Case "$D$10"
                Select Case Target.Value
                    Case Is = 1, 2
                        .CheckBoxes("Check Box 7").Visible = False
                        .CheckBoxes("Check Box 8").Visible = False
                        .Rows("33:34").EntireRow.Hidden = False
                    Case Is = 3, 4
                        .CheckBoxes("Check Box 9").Visible = True
                        .CheckBoxes("Check Box 10").Visible = True
                        .Rows("33:34").EntireRow.Hidden = True
                        .Rows("31:32").EntireRow.Hidden = False
                End Select
            Case "$H$20"
                Select Case Target.Value
                    Case "PRODUCT A"
                        .CheckBoxes("Check Box 5").Visible = False
                        .CheckBoxes("Check Box 6").Visible = False
                        .CheckBoxes("Check Box 1").Visible = True
                        .CheckBoxes("Check Box 2").Visible = True
                        .Rows("21:22").EntireRow.Hidden = True
                    Case "PRODUCT B"
                        .CheckBoxes("Check Box 5").Visible = True
                        .CheckBoxes("Check Box 6").Visible = True
                        .CheckBoxes("Check Box 1").Visible = True
                        .CheckBoxes("Check Box 2").Visible = True
                        .Rows("21:22").EntireRow.Hidden = False
                End Select
        End Select
    End With
    ActiveSheet.Protect Password:="MyPassword"
    Application.ScreenUpdating = True

 

[6StringJazzer]

If the sheet is protected, you can't hide the rows. In the code first you have to

Unprotect

then after hiding

Protect

I see mumps beat me to the actual code.

 

[caet_]

Thank you very much, it solved my problem ?

 

[mumps]

Glad we could help.