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

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

Thank you for your help David. Your assumption is correct. I want to hide all
sections except the one that applies to the value selected in my list.

I pasted the code into the macro and I could not get anything to happen when
I changed the value of "vUtility_Company". Next I tried to run the macro (I
thought maybe I had to initiate it?) but I only got an error. When I tried to
run it manually I am getting an "Invalid use of Me keyword." error.

Any ideas?

"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
>

Hello David,

Sorry about the message earlier, I didn't realize I needed to paste the code
directly into the code for that sheet. Once I did that everything you wrote
worked perfectly. Thanks again for the help!

"Eric" wrote:

> Thank you for your help David. Your assumption is correct. I want to hide all
> sections except the one that applies to the value selected in my list.
>
> I pasted the code into the macro and I could not get anything to happen when
> I changed the value of "vUtility_Company". Next I tried to run the macro (I
> thought maybe I had to initiate it?) but I only got an error. When I tried to
> run it manually I am getting an "Invalid use of Me keyword." error.
>
> Any ideas?
>
> "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
> >

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
>

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