Refresh a Validation List?

[jhollin1138]

I have a spreadsheet that has 2 Validation lists. The first list is to
select an item for a group (example: animal, vegetable or mineral). The
second list is dependant on the first list and displays options available
for the selected group (example: animal is selected in list 1; cat, monkey,
horse, etc. is available in list 2). I have this part of the spreadsheet
working, put when I change the selected item in list 1, list 2 still
displays the last item selected prior to selecting a new item in Validation
list 1. Is there a way to refresh list 2 (or unselect the previously
selected item), after list 1 is changed?

TIA,

 

[Jim Rech]

I believe you'd have to use a macro to get the result you want. The sheet's
change event handler would have to see if the first validation cell is the
one that triggered it and, if so, change the validation list of the second,
as well as its contents.

--
Jim Rech
Excel MVP
|I have a spreadsheet that has 2 Validation lists. The first list is to
| select an item for a group (example: animal, vegetable or mineral). The
| second list is dependant on the first list and displays options available
| for the selected group (example: animal is selected in list 1; cat,
monkey,
| horse, etc. is available in list 2). I have this part of the spreadsheet
| working, put when I change the selected item in list 1, list 2 still
| displays the last item selected prior to selecting a new item in
Validation
| list 1. Is there a way to refresh list 2 (or unselect the previously
| selected item), after list 1 is changed?
|
| TIA,
|
| ----------
| Jim H
|
|

 

[Jason Morin]

Try this macro. It assumes the first list (group) is in
A1 and the dependent list is in B1:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.[A1]) Is Nothing Then
Application.EnableEvents = False
Me.[B1].Value = ActiveWorkbook.Names(Target.Value).Value
Application.EnableEvents = True
End If
End Sub

---
To use, right-click on the worksheet tab, select "View
Code", and paste in the code above. Press ALT+Q to exit.

HTH
Jason
Atlanta, GA

 

[jhollin1138]

Jason

That works, thanks for the help!