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.
 

Fluff

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

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

kkyuvaraj

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
Just Change Pword to match your password
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
      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

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

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

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
You're welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,589
Messages
5,625,671
Members
416,125
Latest member
NeedExcelHelp2021

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