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

[mexiberg]

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

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

 

[Yongle]

If sheet protection is the cause of your problem ... try unprotecting the sheet whilst the VBA is making changes to the sheet

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?

ActiveSheet.Activate

 

[mexiberg]

Thank you! Me. Protect, not sheet.Protect.
Oh and thank you for picking up on that rogue line. Not sure why it was there.

 

[Yongle]

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.

 

[seafood]

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.