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

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