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
|