Run-Time error '1004' - unable to set the hidden property error

mexiberg

New Member
Joined
Feb 22, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone.
Love the forum. I am usually on the outside checking out the solutions to help resolve my own creations, but this one has me stumped.
The code works perfectly when the sheet is unprotected. However as protection is a pre-requisite, I keep encountering the ol' dreaded
"Run-time errror '1004': Unable to set the Hidden property of the Range class"

I have tried a number of options to work around this but with no success.
I hope someone can help me figure it out.

The macro is tied to a dropdown list. User selection then displays one of 3 alternatives.
There is also a command button which is tied to the table which allows the user to add rows. This functions as required without issue. I have its visibility included in the code, just to ensure it stays hidden until needed.

thanks
Nik

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Activate
If Not Application.Intersect(Range("MobileSelection"), Range(Target.Address)) Is Nothing Then
        Select Case Target.Value
        Case Is = "-- Selection Required --":
        Rows("15:28").EntireRow.Hidden = True
        Columns("M:U").EntireColumn.Hidden = True
        CommandButton1.Visible = False
            Case Is = "Mobile Fleet Without Porting":
            Rows("15:28").EntireRow.Hidden = False
            Columns("M:U").EntireColumn.Hidden = True
            CommandButton1.Visible = True
                Case Is = "Mobile Fleet With Porting":
                Rows("15:28").EntireRow.Hidden = False
                Columns("M:U").EntireColumn.Hidden = False
                CommandButton1.Visible = True
        End Select
End If

End Sub
 
Last edited by a moderator:

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
If sheet protection is the cause of your problem ... try unprotecting the sheet whilst the VBA is making changes to the sheet
Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Range("MobileSelection"), Range(Target.Address)) Is Nothing Then

Me.Unprotect "CaseSensitivePassword"
    Select Case Target.Value
        Case Is = "-- Selection Required --":
            Rows("15:28").EntireRow.Hidden = True
            Columns("M:U").EntireColumn.Hidden = True
            CommandButton1.Visible = False
        Case Is = "Mobile Fleet Without Porting":
            Rows("15:28").EntireRow.Hidden = False
            Columns("M:U").EntireColumn.Hidden = True
            CommandButton1.Visible = True
        Case Is = "Mobile Fleet With Porting":
            Rows("15:28").EntireRow.Hidden = False
            Columns("M:U").EntireColumn.Hidden = False
            CommandButton1.Visible = True
    End Select
Me.Protect "CaseSensitivePassword"

End If

End Sub

Why is this line in your code?
VBA Code:
ActiveSheet.Activate
 
Upvote 0
Solution
Thank you! Me. Protect, not sheet.Protect.
Oh and thank you for picking up on that rogue line. Not sure why it was there.
 
Upvote 0
The correct syntax:

Sheets("Name of Sheet").Protect

Using variable :
Set ws = Sheets("Name of Sheet")
ws.Protect

Me in Sheet code window refers to that sheet.
Used in ThisWorkbook code window, Me refers to that workbook.
In userform code, Me refers to that userform.
 
Upvote 0
Thank you Yongle!!! My workbook has macros and hidden rows. Unprotected the code runs fine but once I protect my sheet the error 1004 unable to set the hidden.... I simple inserted the Me. Unprotect... and Me. protect.... and it worked! I have been hours trying to figure this error out. Greatly appreciated.
 
Upvote 0

Forum statistics

Threads
1,224,522
Messages
6,179,297
Members
452,903
Latest member
Knuddeluff

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