Multiple Worksheet Events VBA code in same sheet

[kkyuvaraj]

Hi,

I have below code to hide the Rows 25 to 28 based on the selection in the Cell E24(Like If ,

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E24")) Is Nothing Then Exit Sub

Select Case Target.Value
Case "No"
Rows("25:28").EntireRow.Hidden = True

Case "Yes"
Rows("25:28").EntireRow.Hidden = False
End Select
End Sub

However my requirement is to apply this same logic in many rows in the same excel sheet.Eg. In place of E30 is "Yes" then Rows from 31 to 38 to be hide,E49 is "Yes", then Rows 50 to 60 to be hide and so on at many Rows inn same excel.

Can anyone help on this at the earliest.

 

[Fluff]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("E:E")) Is Nothing Then
      Select Case Target.Address(0, 0)
         Case "E24"
            Rows("25:28").Hidden = Target.Value <> "No"
         Case "E30"
            Rows("31:38").Hidden = Target.Value <> "Yes"
         Case "E49"
            Rows("50:55").Hidden = (Target.Value <> "No" And Target.Value <> "Yes")
      End Select
   End If
End Sub

 

[kkyuvaraj]

Hi,

Thanks a Ton..............The code works perfectly as my requirement.

But if the sheet is protected with password it is not working may I know why and how to solve this.

How to allow users to select any option without unprotecting the sheet.

 

[Fluff]

Just Change Pword to match your password

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("E:E")) Is Nothing Then
      Me.Unprotect "Pword"
      Select Case Target.Address(0, 0)
         Case "E24"
            Rows("25:28").Hidden = Target.Value <> "No"
         Case "E30"
            Rows("31:38").Hidden = Target.Value <> "Yes"
         Case "E49"
            Rows("50:55").Hidden = (Target.Value <> "No" And Target.Value <> "Yes")
      End Select
      Me.Protect "Pword"
   End If
End Sub

 

[kkyuvaraj]

if I use above code with password, after i select answer in E24, it is not allowing to change anything answer and asking to enter password everytime.

My requirement is to allow user to change any answer in "E" column like Yes,No and even to make it blank but the sheet should not unprotect.

Can you help on this.

 

[Fluff]

You need to unlock the cells that users are allowed to change.

 

[kkyuvaraj]

Ok.....Thank You

 

[Fluff]

You're welcome.