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:

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,432
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,432
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,127,354
Messages
5,624,195
Members
416,017
Latest member
moritz210

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