VBA question

jdmchic00

New Member
Joined
May 18, 2020
Messages
6
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hope all is well!
Working on a sheet currently with VBA- I'm trying to hide and show cells depending on value selected in a cell. The problem I'm having is when I make a selection in the selected cell to select which region, unless I click "All" before going to a different region, it ignores my command and shows cells not needing to be shown. Is there a way to correct this? Here is what I have:


VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Application.Intersect(Range("C3"), Target) Is Nothing Then
      Select Case Target.Value
         Case "All"
            Rows("14:42").EntireRow.Hidden = True
            Rows("8:13").EntireRow.Hidden = False
         Case "Region 1"
            Rows("14:17").EntireRow.Hidden = False
            Rows("8:13").EntireRow.Hidden = True
         Case "Region 2"
            Rows("18:21").EntireRow.Hidden = False
            Rows("8:17").EntireRow.Hidden = True
         Case "Region 3"
            Rows("39:42").EntireRow.Hidden = False
            Rows("32:38").EntireRow.Hidden = True
         Case "Region 4"
            Rows("22:31").EntireRow.Hidden = False
            Rows("32:42").EntireRow.Hidden = True
         Case "Region 5"
            Rows("32:38").EntireRow.Hidden = False
            Rows("18:21").EntireRow.Hidden = True
      End Select
   ElseIf Not Application.Intersect(Range("B52"), Target) Is Nothing Then
      Select Case Target.Value
         Case "Advanced Stage"
            Rows("60:62").EntireRow.Hidden = False
            Rows("58:59").EntireRow.Hidden = True
         Case "Early Stage"
            Rows("58:59").EntireRow.Hidden = False
            Rows("60:62").EntireRow.Hidden = True
         Case "Total Open Pipeline"
            Rows("58:62").EntireRow.Hidden = False
      End Select
   End If
End Sub
 
Last edited by a moderator:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,701
Office Version
  1. 365
Platform
  1. Windows
You need to change each case so that is covers all of the rows in the range 8:42.

If you post any more code please remember to tag it correctly in order to preserve formatting and make it legible. You can do this by clicking the </> icon and pasting your code into the popup box instead of pasting it straight into your reply.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,701
Office Version
  1. 365
Platform
  1. Windows
No big deal, you're quite new to the forum. There are some regular posters who still don't have the hang of it :eek:

Do you understand what I said about the changes that you need to make to the code?
 

jdmchic00

New Member
Joined
May 18, 2020
Messages
6
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
haha.. i bet!

I did. A simple overlook I suppose. I got it now. Thanks again. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,400
Messages
5,641,924
Members
417,247
Latest member
Chitaah

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