Rin-Time Error '1004'

[jimonfly]

Just started getting this error today after several weeks of trying to finish a workbook I inherited. I tried several additions from help I received previously from the Board, but can't figure it out.

Unable to set the Hidden property of the Range class

Private Sub Worksheet_Activate()
Call SalesHideCol
End Sub
Sub SalesHideCol()
Dim Rng As Range
Dim c As Range
' Adjust the range to suit
Me.Unprotect Password:="Hermes"
Application.ScreenUpdating = False
Set Rng = Range("G1:K1")
For Each c In Rng
' Determine Criteria
If c.Value = "H" Then
c.EntireColumn.Hidden = True
Else
c.EntireColumn.Hidden = False
End If
Me.Protect Password:="Hermes"
Application.ScreenUpdating = False
Next c
End Sub

When I click Debug, the c.EntireColumn.Hidden = False row is highlighted.

The changes I made today are protecting the individual sheets with my password. Any ideas?

Thanks, Jim

 

[AlphaFrog]

Sub SalesHideCol()

    Dim c      As Range
    
    ActiveSheet.Unprotect Password:="Hermes"
    Application.ScreenUpdating = False
    For Each c In Range("G1:K1") ' Adjust the range to suit
        ' Determine Criteria
        c.EntireColumn.Hidden = c.Value = "H"
    Next c
    Application.ScreenUpdating = True
    ActiveSheet.Protect Password:="Hermes"
    
End Sub

 

[jimonfly]

Thanks, works perfectly. If its not too much trouble to ask, was the code I had incorrect, or just a more complicated route. It looks like yours is much more compact.

I didn't write the code, but it is incorporated in a similar fashion throughout. I'm not sure why the error popped up today as opposed to the last time I made changes.

Thanks again. Jim.

 

[AlphaFrog]

In your original code, the line...

Me.Protect Password:="Hermes"

...is located within the For-Next loop. So you initially Unptotect the sheet then run the loop. Then on the first pass of the loop, the sheet is re-protected. On subsequent passes of the loop, you wouldn't be able to Hide\Unhide columns because the sheet is now protected causing an error.

If you move the line Me.Protect Password:="Hermes" after the line Next c, your original code should work.

 

[jimonfly]

Thanks again. That's something I'll remember to look for.

Jim