|
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? |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 04:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com