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:

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

mexiberg

New Member
Joined
Feb 22, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,389
Members
416,026
Latest member
melvic69

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