Multiple Worksheet Events VBA code in same sheet

kkyuvaraj

New Member
Joined
Apr 25, 2019
Messages
37
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.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In what way didn't it work?
 

kkyuvaraj

New Member
Joined
Apr 25, 2019
Messages
37
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Oops, missed abit, try
VBA Code:
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

New Member
Joined
Apr 25, 2019
Messages
37
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.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
Should the rows be hidden if the cell is Yes or should they be visible if the cell is Yes?
 

kkyuvaraj

New Member
Joined
Apr 25, 2019
Messages
37
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,241
Messages
5,600,503
Members
414,384
Latest member
GrandmasCookies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top