Just a tweak in a HideRows VBA Code

[mikeb7500]

Happy New Year! I feel I'm close but getting an Run-Time '1004": error message. "Unable to Set the Hidden property of the Range class."

I want to SHOW rows 25:33 if the G23 says "Yes", if it says anything else, I want rows 25:33 to be HIDDEN.

I feel I'm close but need a bit of tweaking. Please help...THANKS!

Private Sub worksheet_change(ByVal target As Range)
Dim KeyCells As Range
Set KeyCells = Range("G23")

If Not Application.Intersect(KeyCells, Range(target.Address)) Is Nothing Then
If KeyCells = "Yes" Then
Rows("25:33").Hidden = False
Else
Rows("25:33").Hidden = True
End If
End If

End Sub

 

[dmt32]

I feel I'm close but getting an Run-Time '1004": error message. "Unable to Set the Hidden property of the Range class."

Hi
Is the sheet protected?

Dave

 

[mikeb7500]

Hi
Is the sheet protected?

Dave

Yes, it IS a protected sheet.

 

[mikeb7500]

Yes, it IS a protected sheet.

The code works when it's unprotected, but doesn't when I protect it.

 

[dmt32]

The code works when it's unprotected, but doesn't when I protect it.

Try adding some additional code that unprotects the worksheet

untested but something like this maybe

Private Sub worksheet_change(ByVal Target As Range)
    Dim KeyCells    As Range
    
    Const MyPassword As String = "YourPasswordHere"
    
    Set KeyCells = Range("G23")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        Me.Unprotect MyPassword
        
        If KeyCells = "Yes" Then
            Rows("25:33").Hidden = False
        Else
            Rows("25:33").Hidden = True
        End If
    End If
    
    Me.Protect MyPassword
End Sub

Dave

 

[Peter_SSs]

@mikeb7500
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags in post 1 for you this time.


I also suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


There is no need for Range(Target.Address) since target already is a range. Instead, you can just use this.

If Not Application.Intersect(KeyCells, target) Is Nothing Then

Finally, you could replace all this code

If KeyCells = "Yes" Then
Rows("25:33").Hidden = False
Else
Rows("25:33").Hidden = True
End If
End If

with this

Rows("25:33").Hidden = KeyCells <> "Yes"