Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation List Hide Unhide Rows
Hello Dave,
How would I set up another Worksheet_Change on the same sheet for a different cell value? This one is called "vOwnRent" Thanks again "Dave Peterson" wrote: One problem is that in those subroutines (like PGE_Res) is that the way you got there was that "PGE Residential" was typed into that vUtility_Company cell. That means that the "if" portion of this statement has to be true (or you wouldn't be in that routine. And the Else portion will never be true. (I am assuming that those subroutines are not called by any other routine.) Sub PGE_Res() If [vUtility_Company] = "PGE Residential" Then Rows("31:63").Select Selection.EntireRow.Hidden = False ElseIf [vUtility_Company] < "PGE Residential" Then Rows("30:64").Select Selection.EntireRow.Hidden = True End If Maybe something like: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("vUtility_Company")) Is Nothing Then Exit Sub End If Select Case LCase(Me.Range("vUtility_Company").Value) Case "" Call PS_MinimizeALL Case LCase("PGE Residential") Call PGE_Res Case LCase("PGE Business") Call PGE_Bus Case Else 'do nothing, just continue to the end sub End Select End Sub Sub PS_MinimizeALL() Me.Rows("31:63").EntireRow.Hidden = True End Sub Sub PGE_Res() Me.Rows("31:63").EntireRow.Hidden = False End Sub Sub PGE_Bus() Me.Rows("31:63").EntireRow.Hidden = True End Sub =============== I gotta feeling that you really want to unhide/hide some other rows at the same time. You may want to show everything and then hide just the ones you want: Sub PS_MinimizeALL() Me.Rows.Hidden = False Me.Rows("31:63").Hidden = True End Sub Eric wrote: Hello, Let me start by saying, I have years of excel experience, but very little VBA experience. I currently have a data validation list with 8 options. My goal is to have the user select an option, then have a section expand just below that, based on what they select. I have attempted to write a macro to do this, but failed miserably. (I decided to start with the first 2 options until I could get those to work) I don't know if this is even close, but this is what I have started: Private Sub Worksheet_Change(ByVal Target As Range) Select Case Range("vUtility_Company").Value Case "" PS_MinimizeALL Case "PGE Residential" PGE_Res Case "PGE Business" PGE_Bus Case Else Exit Sub End Select End Sub Sub PS_MinimizeALL() If [vUtility_Company] = "" Then Rows("31:63").Select Selection.EntireRow.Hidden = True End If End Sub Sub PGE_Res() If [vUtility_Company] = "PGE Residential" Then Rows("31:63").Select Selection.EntireRow.Hidden = False ElseIf [vUtility_Company] < "PGE Residential" Then Rows("30:64").Select Selection.EntireRow.Hidden = True End If End Sub Sub PGE_Bus() If [vUtility_Company] = "PGE Business" Then Rows("31:63").Select Selection.EntireRow.Hidden = True ElseIf [vUtility_Company] < "PGE Business" Then Rows("30:64").Select Selection.EntireRow.Hidden = False End If End Sub -- Dave Peterson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation List Hide Unhide Rows
You only get one worksheet event per worksheet. But that procedure can have as
many branches as you need. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If not (Intersect(Target, Me.Range("vUtility_Company")) Is Nothing) Then Select Case LCase(Me.Range("vUtility_Company").Value) Case "" Call PS_MinimizeALL Case LCase("PGE Residential") Call PGE_Res Case LCase("PGE Business") Call PGE_Bus Case Else 'do nothing, just continue to the end sub End Select else if not (intersect(target, me.range("vOwnRent")) is nothing) then 'do what you want for vOwnRent end if end if End Sub Eric wrote: Hello Dave, How would I set up another Worksheet_Change on the same sheet for a different cell value? This one is called "vOwnRent" Thanks again "Dave Peterson" wrote: One problem is that in those subroutines (like PGE_Res) is that the way you got there was that "PGE Residential" was typed into that vUtility_Company cell. That means that the "if" portion of this statement has to be true (or you wouldn't be in that routine. And the Else portion will never be true. (I am assuming that those subroutines are not called by any other routine.) Sub PGE_Res() If [vUtility_Company] = "PGE Residential" Then Rows("31:63").Select Selection.EntireRow.Hidden = False ElseIf [vUtility_Company] < "PGE Residential" Then Rows("30:64").Select Selection.EntireRow.Hidden = True End If Maybe something like: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("vUtility_Company")) Is Nothing Then Exit Sub End If Select Case LCase(Me.Range("vUtility_Company").Value) Case "" Call PS_MinimizeALL Case LCase("PGE Residential") Call PGE_Res Case LCase("PGE Business") Call PGE_Bus Case Else 'do nothing, just continue to the end sub End Select End Sub Sub PS_MinimizeALL() Me.Rows("31:63").EntireRow.Hidden = True End Sub Sub PGE_Res() Me.Rows("31:63").EntireRow.Hidden = False End Sub Sub PGE_Bus() Me.Rows("31:63").EntireRow.Hidden = True End Sub =============== I gotta feeling that you really want to unhide/hide some other rows at the same time. You may want to show everything and then hide just the ones you want: Sub PS_MinimizeALL() Me.Rows.Hidden = False Me.Rows("31:63").Hidden = True End Sub Eric wrote: Hello, Let me start by saying, I have years of excel experience, but very little VBA experience. I currently have a data validation list with 8 options. My goal is to have the user select an option, then have a section expand just below that, based on what they select. I have attempted to write a macro to do this, but failed miserably. (I decided to start with the first 2 options until I could get those to work) I don't know if this is even close, but this is what I have started: Private Sub Worksheet_Change(ByVal Target As Range) Select Case Range("vUtility_Company").Value Case "" PS_MinimizeALL Case "PGE Residential" PGE_Res Case "PGE Business" PGE_Bus Case Else Exit Sub End Select End Sub Sub PS_MinimizeALL() If [vUtility_Company] = "" Then Rows("31:63").Select Selection.EntireRow.Hidden = True End If End Sub Sub PGE_Res() If [vUtility_Company] = "PGE Residential" Then Rows("31:63").Select Selection.EntireRow.Hidden = False ElseIf [vUtility_Company] < "PGE Residential" Then Rows("30:64").Select Selection.EntireRow.Hidden = True End If End Sub Sub PGE_Bus() If [vUtility_Company] = "PGE Business" Then Rows("31:63").Select Selection.EntireRow.Hidden = True ElseIf [vUtility_Company] < "PGE Business" Then Rows("30:64").Select Selection.EntireRow.Hidden = False End If End Sub -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enabling option „Format rows“ to hide/unhide rows using VBA-code? | Excel Discussion (Misc queries) | |||
unhide rows based on data validation | Excel Programming | |||
Hide/Show Rows based on Cell Value with Data Validation | Excel Programming | |||
Hide Rows (Current Date) / Unhide Rows | Excel Programming | |||
Hide Columns based on a Data Validation List | Excel Programming |