MitchiBoy3
New Member
- Joined
- Feb 24, 2020
- Messages
- 4
- Office Version
- 2019
- Platform
- Windows
Good day experts!
I have this simple excel that will hide and unhide specific rows based on a value (data list). When my sheet is protected, I cant hide and unhide rows. Below is my VBA code.
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("bd5").Value = 1 Then
Rows("9:10").EntireRow.Hidden = False
Rows("14:30").EntireRow.Hidden = True
ElseIf Range("bd5").Value = 2 Then
Rows("9:10").EntireRow.Hidden = False
Rows("14:30").EntireRow.Hidden = False
Rows("14:14").EntireRow.Hidden = True
Rows("17:30").EntireRow.Hidden = True
ElseIf Range("bd5").Value = 3 Then
Rows("14:30").EntireRow.Hidden = False
Rows("14:15").EntireRow.Hidden = True
Rows("18:30").EntireRow.Hidden = True
Rows("9:10").EntireRow.Hidden = True
ElseIf Range("bd5").Value = 4 Then
Rows("9:10").EntireRow.Hidden = False
Rows("14:30").EntireRow.Hidden = False
Rows("14:16").EntireRow.Hidden = True
Rows("22:30").EntireRow.Hidden = True
ElseIf Range("bd5").Value = 5 Then
Rows("9:10").EntireRow.Hidden = False
Rows("14:30").EntireRow.Hidden = False
Rows("14:21").EntireRow.Hidden = True
Rows("28:30").EntireRow.Hidden = True
Rows("9:10").EntireRow.Hidden = True
ElseIf Range("bd5").Value = 6 Then
Rows("9:10").EntireRow.Hidden = False
Rows("14:30").EntireRow.Hidden = False
Rows("14:16").EntireRow.Hidden = True
Rows("22:30").EntireRow.Hidden = True
End If
If Range("BA8").Value = 1 Then
Rows("47:101").EntireRow.Hidden = True
ElseIf Range("BA8").Value = 2 Then
Rows("47:101").EntireRow.Hidden = True
Rows("47:101").EntireRow.Hidden = False
Rows("58:101").EntireRow.Hidden = True
ElseIf Range("BA8").Value = 3 Then
Rows("47:101").EntireRow.Hidden = True
Rows("47:101").EntireRow.Hidden = False
Rows("69:101").EntireRow.Hidden = True
ElseIf Range("BA8").Value = 4 Then
Rows("47:101").EntireRow.Hidden = True
Rows("47:101").EntireRow.Hidden = False
Rows("80:101").EntireRow.Hidden = True
ElseIf Range("BA8").Value = 5 Then
Rows("47:101").EntireRow.Hidden = True
Rows("47:101").EntireRow.Hidden = False
Rows("91:101").EntireRow.Hidden = True
ElseIf Range("BA8").Value = 6 Then
Rows("47:101").EntireRow.Hidden = True
Rows("47:101").EntireRow.Hidden = False
End If
End Sub
Im not expert on VBA but slightly knowledgeable. Please help me on this. Thanks so much!
I have this simple excel that will hide and unhide specific rows based on a value (data list). When my sheet is protected, I cant hide and unhide rows. Below is my VBA code.
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("bd5").Value = 1 Then
Rows("9:10").EntireRow.Hidden = False
Rows("14:30").EntireRow.Hidden = True
ElseIf Range("bd5").Value = 2 Then
Rows("9:10").EntireRow.Hidden = False
Rows("14:30").EntireRow.Hidden = False
Rows("14:14").EntireRow.Hidden = True
Rows("17:30").EntireRow.Hidden = True
ElseIf Range("bd5").Value = 3 Then
Rows("14:30").EntireRow.Hidden = False
Rows("14:15").EntireRow.Hidden = True
Rows("18:30").EntireRow.Hidden = True
Rows("9:10").EntireRow.Hidden = True
ElseIf Range("bd5").Value = 4 Then
Rows("9:10").EntireRow.Hidden = False
Rows("14:30").EntireRow.Hidden = False
Rows("14:16").EntireRow.Hidden = True
Rows("22:30").EntireRow.Hidden = True
ElseIf Range("bd5").Value = 5 Then
Rows("9:10").EntireRow.Hidden = False
Rows("14:30").EntireRow.Hidden = False
Rows("14:21").EntireRow.Hidden = True
Rows("28:30").EntireRow.Hidden = True
Rows("9:10").EntireRow.Hidden = True
ElseIf Range("bd5").Value = 6 Then
Rows("9:10").EntireRow.Hidden = False
Rows("14:30").EntireRow.Hidden = False
Rows("14:16").EntireRow.Hidden = True
Rows("22:30").EntireRow.Hidden = True
End If
If Range("BA8").Value = 1 Then
Rows("47:101").EntireRow.Hidden = True
ElseIf Range("BA8").Value = 2 Then
Rows("47:101").EntireRow.Hidden = True
Rows("47:101").EntireRow.Hidden = False
Rows("58:101").EntireRow.Hidden = True
ElseIf Range("BA8").Value = 3 Then
Rows("47:101").EntireRow.Hidden = True
Rows("47:101").EntireRow.Hidden = False
Rows("69:101").EntireRow.Hidden = True
ElseIf Range("BA8").Value = 4 Then
Rows("47:101").EntireRow.Hidden = True
Rows("47:101").EntireRow.Hidden = False
Rows("80:101").EntireRow.Hidden = True
ElseIf Range("BA8").Value = 5 Then
Rows("47:101").EntireRow.Hidden = True
Rows("47:101").EntireRow.Hidden = False
Rows("91:101").EntireRow.Hidden = True
ElseIf Range("BA8").Value = 6 Then
Rows("47:101").EntireRow.Hidden = True
Rows("47:101").EntireRow.Hidden = False
End If
End Sub
Im not expert on VBA but slightly knowledgeable. Please help me on this. Thanks so much!