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

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" wrote:

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

PERFECT - thank you so much for the quick response!


"Stephen C" wrote:

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" wrote:

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

Your solution also answers my exact same question and works like a charm. I am a novice with VB so for the sake of others like me, let me explain what I did to make this work.

I have cell A as the main drop down list and cell B as the indirect list. I simply copied and pasted your VB code into the Visual Basic sheet of my Excel 2007 worksheet by selecting the DEVELOPER tab and then VISUAL BASIC tab in Excel 2007. I changed the first "A1" reference in your code to my cell A and your cell "A2" reference to my cell B then Saved the sheet. Like I said, it works like a charm. Hopefully my notes will help some other VB novice like me.

Thank you so much!