Re: Data Validation List Hide Unhide Rows by Dave
Dave
Mon Jul 28 22:04:49 CDT 2008
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