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]

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
         Case "E24"
            Rows("25:28").Hidden = Target.Value = "No"
         Case "E30"
            Rows("31:38").Hidden = Target.Value = "No"
      End Select
   End If
End Sub

 

[Fluff]

Cross posted Multiple Worksheet Events in same excel

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

 

[kkyuvaraj]

Hi,

The code id not working

 

[Fluff]

In what way didn't it work?

 

[kkyuvaraj]

I paste this code in worksheet and If i select "Yes" or "No" in E24 or E30, nothing happened. The rows are not hiding as required.

 

[Fluff]

Oops, missed abit, try

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 = "No"
      End Select
   End If
End Sub

 

[kkyuvaraj]

Hi,
First of all Thank you very much for your time to create this code.

Code works fine, however if the cell is blank, the rows are not hidden.

Initially the cell will be blank untill user select the answer, so the rows should remain hidden till the user select "Yes" or "No" in E column and even if the cell is blank then the rows should be Hidden.

Also in some cases, the hidden rows should open if user enter either "Yes" or "No".
For Eg. if E60 is either "Yes" or "No" then the Rows from 61 to 65 should unhide.

Can you pls alter the code accordingly.

 

[Fluff]

Should the rows be hidden if the cell is Yes or should they be visible if the cell is Yes?

 

[kkyuvaraj]

Hi,
The requirement is, the rows should be hidden if the Cell is blank as all the cells in the E column will be blank if user open the workbook first.

There is a drop down in column "E" with answer "Yes","No" & "NA"and user can select any answer.

Required conditions for rows Hide & unhide.

Case 1:
If cell E24 is "No" then the rows 25 to 28 should unhide or else the rows remain Hidden if it is blank or any other answer.

Case 2:
If cell E30 is "Yes", then the rows 31 to 34 should unhide or else the rows remain Hidden if it is blank or any other answer.

Case 3:
If cell E49 is either "Yes" or "No" then the rows 50 to 55 should unhide or else the rows remain Hidden if it is blank.

Like above the Hide & unhide condition may change cell to cell in the entire sheet.

Pls help alter the code accordingly so that i can alter the same as per my requirement.

Thanks in advance