Error 1004 when Protected

[jimonfly]

I am adding sheets to an existing workbook containing Hidden Columns commands. The sheet works fine when it is unprotected, but when protected I get a "Run-time error '1004'" "Unable to set the Hidden property of the class"

The code is below:

Private Sub Worksheet_Activate()

Dim Rng As Range
Dim c As Range
' Adjust the range to suit
Set Rng = Range("a2:a18")


For Each c In Rng
' Determine Criteria
If c.Value = "H" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False

End If
Application.ScreenUpdating = False
Next c
End Sub

When I hit "Debug", the line c.EntireRow.Hidden = False is highlighted in yellow.

Column A contains If Then statements regarding the number of Comparable Rentals used in an appraisal report, reporting either a "S" or "H" in Column A. This portion works fine, and the rows hide correctly when unprotected.

I copied the code above from another sheet, which works correctly protected or not, so it is not my code. However, I am trying to use it on several new sheets, and all give the error. Below is the original code:

Private Sub Worksheet_Activate()
Call SalesHideCol
Call SalesHideRows
End Sub
Sub SalesHideCol()
Dim Rng As Range
Dim c As Range
' Adjust the range to suit
Set Rng = Range("f1:J1")
For Each c In Rng
' Determine Criteria
If c.Value = "H" Then
c.EntireColumn.Hidden = True
Else
c.EntireColumn.Hidden = False

End If
Application.ScreenUpdating = False
Next c
End Sub
Sub SalesHideRows()
Dim Rng As Range
Dim c As Range
' Adjust the range to suit
Set Rng = Range("a3:a54")
For Each c In Rng
' Determine Criteria
If c.Value = "H" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False

End If
Application.ScreenUpdating = False
Next c
End Sub

Does it have something to do with which cells are protected in the Format Cells box? I'm new and still learning VBA, so please bear with me.

 

[VoG]

Try like this

Private Sub Worksheet_Activate()
Dim Rng As Range
Dim c As Range
' Adjust the range to suit
Me.Unprotect Password:="abc"
Application.ScreenUpdating = False
Set Rng = Range("a2:a18")
For Each c In Rng
' Determine Criteria
    c.EntireRow.Hidden = c.Value = "H"
Next c
Me.Protect Password:="abc"
Application.ScreenUpdating = True
End Sub

 

[jimonfly]

Thanks. That worked great.

This may be too much to ask, but why did the code work on the original worksheet, but not on the one that I created? They were both protected and my code came directly from the original.

Jim

 

[VoG]

It may be that different cells were locked on the two sheets.

 

[jimonfly]

Thanks again. I'll apply the new code to the other sheets.

Jim