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

caet_

New Member
Joined
Nov 22, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
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.

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

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,160
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.
Rich (BB code):
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
 
Solution

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
724
Office Version
  1. 365
Platform
  1. Windows
If the sheet is protected, you can't hide the rows. In the code first you have to

VBA Code:
Unprotect

then after hiding

VBA Code:
Protect

I see mumps beat me to the actual code.
 

caet_

New Member
Joined
Nov 22, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
Thank you very much, it solved my problem (y)🙏
 

Watch MrExcel Video

Forum statistics

Threads
1,123,265
Messages
5,600,605
Members
414,393
Latest member
Vignesh Mechz

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
Top