How To Clear Previous Selection From a Drop Down

[The Griffster]

Good Afternoon-

I've created a simple spreadsheet in Excel 2007 which allows the user to
select from several work locations via a drop down. Based on the work
location selection, there are various work schedules they can select from.
This was done using the Indirect function within the data valadation section.

My question is how to clear a work schedule that was previously selected
once a new work location is selected. I'll try to give an example:

Work Locations
California
Florida
Texas


If California is selected they can choose 10 hour Shift or 12 Hour Shift

If the employee then goes back and changes the work location to Florida, the
drop down list changes to 8 Hour Shift, but the result displayed on the
screen still shows whatever was previously selected from the California work
location and won't change until the employee selects from the drop down.

I added a "-" as a work schedule within each of the tables and would like
that to be the default selection each time the work location is changed.

Any help is greatly appreciated.

Thanks-

Griff

 

[Stephen C]

Try the following worksheet change event, remember to change A1 & B1 to suit
your worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, _
Range("A1")) Is Nothing Then '<<<<<Work Locations
Application.EnableEvents = False
Range("B1").Select '<<<<<hour Shift
Selection.ClearContents
End If
Application.EnableEvents = True
End Sub

 

[The Griffster]

PERFECT - thank you so much for the quick response!