When Cells change value, Spreadsheet Rows are Hidden?

R

rguliuzza

New Member
Joined
Mar 12, 2012
Messages
37
Hi there, I have a spreadsheet where there are three drop down lists at cells A13, A15 and C18. Based on the choices, certain rows will be hidden or not. What I am trying to do is if any of the three cells change, the VBA code will run. What I am finding is once a choice is made, if I make another choice, it doesn't automatically change unless I change cell C18? Here is my code:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("C18")) Is Nothing Then Exit Sub
    Application.EnableEvents = False 'to prevent endless loop
Rows("1:2000").EntireRow.Hidden = False
If Range("$C$18").Value = "G3LabUniversalDV" Then
    Range("G3LabU_PC").EntireRow.Hidden = True
    Range("G3ProU_DV").EntireRow.Hidden = True
    Range("G3PC").EntireRow.Hidden = True
    Range("PC_Network").EntireRow.Hidden = True
    Range("TruBioPC").EntireRow.Hidden = True
   End If
If Range("$C$18").Value = "G3LabUniversalPC" Then
    Range("G3LabU_DV").EntireRow.Hidden = True
    Range("G3ProU_DV").EntireRow.Hidden = True
    Range("G3PC").EntireRow.Hidden = True
    Range("DeltaV_Network").EntireRow.Hidden = True
    Range("TruBioDV").EntireRow.Hidden = True
   End If
If Range("$C$18").Value = "G3ProUniversal" Then
    Range("G3LabU_DV").EntireRow.Hidden = True
    Range("G3LabU_PC").EntireRow.Hidden = True
    Range("G3PC").EntireRow.Hidden = True
    Range("PC_Network").EntireRow.Hidden = True
    Range("TruBioPC").EntireRow.Hidden = True
   End If
If Range("$C$18").Value = "G3PC" Then
    Range("G3LabU_DV").EntireRow.Hidden = True
    Range("G3LabU_PC").EntireRow.Hidden = True
    Range("G3ProU_DV").EntireRow.Hidden = True
    Range("DeltaV_Network").EntireRow.Hidden = True
    Range("TruBioDV").EntireRow.Hidden = True
    End If
If Range("$A$13").Value = "No" Then
    Range("SCADA_OPC").EntireRow.Hidden = True
    End If
If Range("$A$15").Value = "No" Then
    Range("Scales").EntireRow.Hidden = True
    End If
    Application.EnableEvents = True
End Sub
Any ideas?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
Code: 
If Intersect(Target, Me.Range("C18")) Is Nothing Then Exit Sub

Here you are saying that you don't want it to run unless C18 is the cell that is changing. What do you actually want the behaviour to be?
 
Upvote 0
BrianMH said:
Code: 
If Intersect(Target, Me.Range("C18")) Is Nothing Then Exit Sub

Here you are saying that you don't want it to run unless C18 is the cell that is changing. What do you actually want the behaviour to be?
Click to expand...
Hi Brian, Thank you for your reply. What I am looking for is when either C18, A13 or A15 change state the cells are automatically hidden. For instance, I pick C18 and the cells hide based on that choice. Then I pick A13 and those cells hide. In saying this, it might be better to have three different VBA modules? What do you think? Thank you, Rick
 
Upvote 0
You can't have 3 individual subs for 1 event but what you can do is.


Code: 
Private Sub Worksheet_Change(ByVal Target As Range)    Select Case Target.Address
        Case "$C$18"
            'do code if its "C18"
        Case "$A$13"
            'do code if its "A13"
        Case "$A$15"
            'do code if its "A15"


    End Select


End Sub
 
Upvote 0
BrianMH said:
You can't have 3 individual subs for 1 event but what you can do is.


Code: 
Private Sub Worksheet_Change(ByVal Target As Range)    Select Case Target.Address
        Case "$C$18"
            'do code if its "C18"
        Case "$A$13"
            'do code if its "A13"
        Case "$A$15"
            'do code if its "A15"


    End Select


End Sub
Click to expand...
Hi Brian, That seems like a great option. So based on this solution, what would I eliminate in my code and replace with this? Thank you, Rick
 
Upvote 0
rguliuzza said:
Hi Brian, I was asking what part of my code would I eliminate and replace with the code you provided. :) Thank you, Rick
Click to expand...
Hi Bryan, I think I figured it out. It seems to work now.
Code: 
 Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False 'to prevent endless loop
 Select Case Target.Address
        Case "$C$18"
            'do code if its "C18"
If Range("$C$18").Value = "G3LabUniversalDV" Then
    Range("63:352").EntireRow.Hidden = False
    Range("G3LabU_PC").EntireRow.Hidden = True
    Range("G3ProU_DV").EntireRow.Hidden = True
    Range("G3PC").EntireRow.Hidden = True
    Range("PC_Network").EntireRow.Hidden = True
    Range("TruBioPC").EntireRow.Hidden = True
        End If
If Range("$C$18").Value = "G3LabUniversalDV" Then
    Range("63:352").EntireRow.Hidden = False
    Range("G3LabU_PC").EntireRow.Hidden = True
    Range("G3ProU_DV").EntireRow.Hidden = True
    Range("G3PC").EntireRow.Hidden = True
    Range("PC_Network").EntireRow.Hidden = True
    Range("TruBioPC").EntireRow.Hidden = True
        End If
If Range("$C$18").Value = "G3LabUniversalPC" Then
    Range("63:352").EntireRow.Hidden = False
    Range("G3LabU_DV").EntireRow.Hidden = True
    Range("G3ProU_DV").EntireRow.Hidden = True
    Range("G3PC").EntireRow.Hidden = True
    Range("DeltaV_Network").EntireRow.Hidden = True
    Range("TruBioDV").EntireRow.Hidden = True
        End If
If Range("$C$18").Value = "G3ProUniversal" Then
    Range("63:352").EntireRow.Hidden = False
    Range("G3LabU_DV").EntireRow.Hidden = True
    Range("G3LabU_PC").EntireRow.Hidden = True
    Range("G3PC").EntireRow.Hidden = True
    Range("PC_Network").EntireRow.Hidden = True
    Range("TruBioPC").EntireRow.Hidden = True
        End If
If Range("$C$18").Value = "G3PC" Then
    Range("63:352").EntireRow.Hidden = False
    Range("G3LabU_DV").EntireRow.Hidden = True
    Range("G3LabU_PC").EntireRow.Hidden = True
    Range("G3ProU_DV").EntireRow.Hidden = True
    Range("DeltaV_Network").EntireRow.Hidden = True
    Range("TruBioDV").EntireRow.Hidden = True
        End If
If Range("$C$18").Value = "ReviseQuote" Then
   Rows.Hidden = False
        End If
        Case "$A$13"
        If Range("$A$13").Value = "No" Then
    Range("SCADA_OPC").EntireRow.Hidden = True
Else
    Range("1076:1100").EntireRow.Hidden = False
        End If
        Case "$A$15"
          If Range("$A$15").Value = "No" Then
    Range("Scales").EntireRow.Hidden = True
Else
    Range("996:1055").EntireRow.Hidden = False
        End If
    
    End Select
Application.EnableEvents = True
    
End Sub
I appreciate your assistance with this. With best regards, Rick
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
ActiveX Checkbox fails after sheetname change
Replies
4
Views
178
bobsan42
bobsan42
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
549
kevin9999
K
M
  • Question
Hide Rows based on cell with drop down values
Replies
1
Views
516
kevin9999
K
J
  • Question
VBA Macro for hiding and unhiding rows is rearranging cells
2
Replies
14
Views
1K
dmt32
D
D
  • Question
VBA to hide rows when a cell is a specific value and then hide the associated worksheet
Replies
3
Views
445
NoSparks
N

Forum statistics

Threads
1,215,775
Messages
6,126,828
Members
449,343
Latest member
DEWS2031

Share this page

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
Back
Top