Data Validation List Hide Unhide Rows

E

Eric

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
 
D

Dave Peterson

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
Click to expand...

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



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
Click to expand...
 
E

Eric

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 said:
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
Click to expand...

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



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
Click to expand...
Click to expand...
 
E

Eric

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 said:
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 said:
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
Click to expand...

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



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
Click to expand...
Click to expand...
Click to expand...
 
E

Eric

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 said:
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
Click to expand...

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



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
Click to expand...
Click to expand...
 
D

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
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 said:
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
Click to expand...

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



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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code error to mirror data from one table to another 6
Mirror value to another cell. 2
Hide Rows Using Checkbox 3
run macro when drop down box equals 2
Macro Trigger 10
Hidden Rows not working correctly 2
Hide and Unhide Rows button 6
Trigger Macro based on single Cell change 5

Top