Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unhiding rows as a result of selection in dropdown list
How can I set something up that when a value from a dropdown list is selected
then a range of rows are unhidden then when another value is selected a different range is unhidden? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unhiding rows as a result of selection in dropdown list
If you're using data|validation, you can use a macro:
If you want to try... Rightclick on the worksheet tab that should have this behavior. Select View code and paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub 'single cell at a time End If If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub End If Me.Rows.Hidden = False Select Case LCase(Target.Value) Case Is = LCase("a") Me.Range("a2").Resize(12).EntireRow.Hidden = True Case Is = LCase("b") Me.Range("a22").Resize(7).EntireRow.Hidden = True End Select End Sub My test routine checks for a change in A1. If it sees "a", then it hides rows 2 to 13 (resizes to 12 rows). If it sees "b", it hides rows 22 to 28 (resizes to 7 rows). It also unhides all the rows before it checks what changed. guz wrote: How can I set something up that when a value from a dropdown list is selected then a range of rows are unhidden then when another value is selected a different range is unhidden? -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unhiding rows as a result of selection in dropdown list
Thanks Dave, this'll solve my problem
"Dave Peterson" wrote: If you're using data|validation, you can use a macro: If you want to try... Rightclick on the worksheet tab that should have this behavior. Select View code and paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub 'single cell at a time End If If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub End If Me.Rows.Hidden = False Select Case LCase(Target.Value) Case Is = LCase("a") Me.Range("a2").Resize(12).EntireRow.Hidden = True Case Is = LCase("b") Me.Range("a22").Resize(7).EntireRow.Hidden = True End Select End Sub My test routine checks for a change in A1. If it sees "a", then it hides rows 2 to 13 (resizes to 12 rows). If it sees "b", it hides rows 22 to 28 (resizes to 7 rows). It also unhides all the rows before it checks what changed. guz wrote: How can I set something up that when a value from a dropdown list is selected then a range of rows are unhidden then when another value is selected a different range is unhidden? -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unhiding rows as a result of selection in dropdown list
I've tried the solution given and it works when I manually input the values.
However a) I want the macro to work from the value selected in the dropdown list b) To further complicate matters I would want something similar with a second dropdown! alan g "Dave Peterson" wrote: If you're using data|validation, you can use a macro: If you want to try... Rightclick on the worksheet tab that should have this behavior. Select View code and paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub 'single cell at a time End If If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub End If Me.Rows.Hidden = False Select Case LCase(Target.Value) Case Is = LCase("a") Me.Range("a2").Resize(12).EntireRow.Hidden = True Case Is = LCase("b") Me.Range("a22").Resize(7).EntireRow.Hidden = True End Select End Sub My test routine checks for a change in A1. If it sees "a", then it hides rows 2 to 13 (resizes to 12 rows). If it sees "b", it hides rows 22 to 28 (resizes to 7 rows). It also unhides all the rows before it checks what changed. guz wrote: How can I set something up that when a value from a dropdown list is selected then a range of rows are unhidden then when another value is selected a different range is unhidden? -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unhiding rows as a result of selection in dropdown list
How did you create the dropdown?
Was it data|Validation? Was it a dropdown from the Forms toolbar? Was it a combobox from the control toolbox toolbar? What are the addresses of the cells with data|validation? What are the names of the dropdowns? What are the names of the comboboxes? What version of excel are you using? guz wrote: I've tried the solution given and it works when I manually input the values. However a) I want the macro to work from the value selected in the dropdown list b) To further complicate matters I would want something similar with a second dropdown! alan g "Dave Peterson" wrote: If you're using data|validation, you can use a macro: If you want to try... Rightclick on the worksheet tab that should have this behavior. Select View code and paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub 'single cell at a time End If If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub End If Me.Rows.Hidden = False Select Case LCase(Target.Value) Case Is = LCase("a") Me.Range("a2").Resize(12).EntireRow.Hidden = True Case Is = LCase("b") Me.Range("a22").Resize(7).EntireRow.Hidden = True End Select End Sub My test routine checks for a change in A1. If it sees "a", then it hides rows 2 to 13 (resizes to 12 rows). If it sees "b", it hides rows 22 to 28 (resizes to 7 rows). It also unhides all the rows before it checks what changed. guz wrote: How can I set something up that when a value from a dropdown list is selected then a range of rows are unhidden then when another value is selected a different range is unhidden? -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unhiding rows as a result of selection in dropdown list
I've solved part a) of my follow-up query, however I can't get part b) to
work i.e. how can I replicate the row unhide for a value for a secondary drop down in the macro. To reply to your questions (in reverse order) Excel 2003 (SP3) Cell B3 is validated through data validation with a source = changetype which is a list held on a separate sheet containing the values (New, Amendment and Update) Cell E3 is validated through data validation with a source = system which is a list held on a separate sheet containing the values (Systema, Systemb, Systemc) "Dave Peterson" wrote: How did you create the dropdown? Was it data|Validation? Was it a dropdown from the Forms toolbar? Was it a combobox from the control toolbox toolbar? What are the addresses of the cells with data|validation? What are the names of the dropdowns? What are the names of the comboboxes? What version of excel are you using? guz wrote: I've tried the solution given and it works when I manually input the values. However a) I want the macro to work from the value selected in the dropdown list b) To further complicate matters I would want something similar with a second dropdown! alan g "Dave Peterson" wrote: If you're using data|validation, you can use a macro: If you want to try... Rightclick on the worksheet tab that should have this behavior. Select View code and paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub 'single cell at a time End If If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub End If Me.Rows.Hidden = False Select Case LCase(Target.Value) Case Is = LCase("a") Me.Range("a2").Resize(12).EntireRow.Hidden = True Case Is = LCase("b") Me.Range("a22").Resize(7).EntireRow.Hidden = True End Select End Sub My test routine checks for a change in A1. If it sees "a", then it hides rows 2 to 13 (resizes to 12 rows). If it sees "b", it hides rows 22 to 28 (resizes to 7 rows). It also unhides all the rows before it checks what changed. guz wrote: How can I set something up that when a value from a dropdown list is selected then a range of rows are unhidden then when another value is selected a different range is unhidden? -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unhiding rows as a result of selection in dropdown list
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub 'single cell at a time End If If Not (Intersect(Target, Me.Range("b3")) Is Nothing) Then 'changing B3 Me.Rows.Hidden = False Select Case LCase(Target.Value) Case Is = LCase("New") Me.Range("a2").Resize(12).EntireRow.Hidden = True Case Is = LCase("Amendment") Me.Range("a22").Resize(7).EntireRow.Hidden = True Case Is = LCase("Update") Me.Range("a22").Resize(7).EntireRow.Hidden = True End Select ElseIf Not (Intersect(Target, Me.Range("e3")) Is Nothing) Then 'changing e3 'your code that does other stuff here 'if you're changing another cell 'do this surrounding the change Application.EnableEvents = False Me.Range("F3").Value = "changed to something else" Application.EnableEvents = True 'to stop your change via code from firing this event End If End Sub guz wrote: I've solved part a) of my follow-up query, however I can't get part b) to work i.e. how can I replicate the row unhide for a value for a secondary drop down in the macro. To reply to your questions (in reverse order) Excel 2003 (SP3) Cell B3 is validated through data validation with a source = changetype which is a list held on a separate sheet containing the values (New, Amendment and Update) Cell E3 is validated through data validation with a source = system which is a list held on a separate sheet containing the values (Systema, Systemb, Systemc) "Dave Peterson" wrote: How did you create the dropdown? Was it data|Validation? Was it a dropdown from the Forms toolbar? Was it a combobox from the control toolbox toolbar? What are the addresses of the cells with data|validation? What are the names of the dropdowns? What are the names of the comboboxes? What version of excel are you using? guz wrote: I've tried the solution given and it works when I manually input the values. However a) I want the macro to work from the value selected in the dropdown list b) To further complicate matters I would want something similar with a second dropdown! alan g "Dave Peterson" wrote: If you're using data|validation, you can use a macro: If you want to try... Rightclick on the worksheet tab that should have this behavior. Select View code and paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub 'single cell at a time End If If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub End If Me.Rows.Hidden = False Select Case LCase(Target.Value) Case Is = LCase("a") Me.Range("a2").Resize(12).EntireRow.Hidden = True Case Is = LCase("b") Me.Range("a22").Resize(7).EntireRow.Hidden = True End Select End Sub My test routine checks for a change in A1. If it sees "a", then it hides rows 2 to 13 (resizes to 12 rows). If it sees "b", it hides rows 22 to 28 (resizes to 7 rows). It also unhides all the rows before it checks what changed. guz wrote: How can I set something up that when a value from a dropdown list is selected then a range of rows are unhidden then when another value is selected a different range is unhidden? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unhiding rows as a result of selection in dropdown list
Great! This now does exactly what I want. Thanks.
"Dave Peterson" wrote: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub 'single cell at a time End If If Not (Intersect(Target, Me.Range("b3")) Is Nothing) Then 'changing B3 Me.Rows.Hidden = False Select Case LCase(Target.Value) Case Is = LCase("New") Me.Range("a2").Resize(12).EntireRow.Hidden = True Case Is = LCase("Amendment") Me.Range("a22").Resize(7).EntireRow.Hidden = True Case Is = LCase("Update") Me.Range("a22").Resize(7).EntireRow.Hidden = True End Select ElseIf Not (Intersect(Target, Me.Range("e3")) Is Nothing) Then 'changing e3 'your code that does other stuff here 'if you're changing another cell 'do this surrounding the change Application.EnableEvents = False Me.Range("F3").Value = "changed to something else" Application.EnableEvents = True 'to stop your change via code from firing this event End If End Sub guz wrote: I've solved part a) of my follow-up query, however I can't get part b) to work i.e. how can I replicate the row unhide for a value for a secondary drop down in the macro. To reply to your questions (in reverse order) Excel 2003 (SP3) Cell B3 is validated through data validation with a source = changetype which is a list held on a separate sheet containing the values (New, Amendment and Update) Cell E3 is validated through data validation with a source = system which is a list held on a separate sheet containing the values (Systema, Systemb, Systemc) "Dave Peterson" wrote: How did you create the dropdown? Was it data|Validation? Was it a dropdown from the Forms toolbar? Was it a combobox from the control toolbox toolbar? What are the addresses of the cells with data|validation? What are the names of the dropdowns? What are the names of the comboboxes? What version of excel are you using? guz wrote: I've tried the solution given and it works when I manually input the values. However a) I want the macro to work from the value selected in the dropdown list b) To further complicate matters I would want something similar with a second dropdown! alan g "Dave Peterson" wrote: If you're using data|validation, you can use a macro: If you want to try... Rightclick on the worksheet tab that should have this behavior. Select View code and paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub 'single cell at a time End If If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub End If Me.Rows.Hidden = False Select Case LCase(Target.Value) Case Is = LCase("a") Me.Range("a2").Resize(12).EntireRow.Hidden = True Case Is = LCase("b") Me.Range("a22").Resize(7).EntireRow.Hidden = True End Select End Sub My test routine checks for a change in A1. If it sees "a", then it hides rows 2 to 13 (resizes to 12 rows). If it sees "b", it hides rows 22 to 28 (resizes to 7 rows). It also unhides all the rows before it checks what changed. guz wrote: How can I set something up that when a value from a dropdown list is selected then a range of rows are unhidden then when another value is selected a different range is unhidden? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
selection from dropdown list activates hyperlink | Excel Discussion (Misc queries) | |||
How to copy or move a row on a particular dropdown list selection | Excel Discussion (Misc queries) | |||
result of selecting from the dropdown list should be a dropdown list | Excel Worksheet Functions | |||
result of selecting from the dropdown list should be a dropdown list | Excel Worksheet Functions | |||
Dropdown list key selection | Excel Discussion (Misc queries) |