Perfect! Thank you so much. Toni

"Ron Coderre" wrote:

Try this:

'--------start of code----------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCellCount As Integer

On Error GoTo ErrTrap

'Check if the active cell is one of the Data Validation cells
'But not the last one
If Not Intersect(Target, Range("C4:D90")) Is Nothing Then

'Count the DV cells to the right of the Active Cell
intCellCount = Range(ActiveCell, "D4").Columns.Count

'Select the cell to the right of the active cell
ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Select

'Turn off events so you don't get into an endless loop
Application.EnableEvents = False
With ActiveCell
'Clear the contents of the subsequent DV cells
.Resize(RowSize:=1, ColumnSize:=intCellCount) _
.ClearContents
End With
'Alert the user that an item must be selected
MsgBox _
Title:="Notice", _
Prompt:="You must now select an item from the next list", _
Buttons:=vbInformation + vbOKOnly
Application.SendKeys ("%{DOWN}")

End If

ErrTrap:
Application.EnableEvents = True
End Sub
'--------end of code----------

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Toni" wrote:

Thanks, Ron. It works -- sort of. The range is C4:E90. I changed the
target range to C4:E90, but it only works in C4:E10. Thanks for all your
patience and help.

Toni

"Ron Coderre" wrote:

If my suggestions/assumptions are correct and you would adjust cells C1:E1 in
sequential order, then try the below code. :

Note: It might be overkill but this code pops up a nag window to tell the
user to finish selecting items. If it's too annoying, just comment out the
MSGBOX section, below (Leave the SEND KEYS code though).

'--------start of code----------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCellCount As Integer

On Error GoTo ErrTrap

'Check if the active cell is one of the Data Validation cells
'But not the last one
If Not Intersect(Target, Range("C1:D1")) Is Nothing Then

'Count the DV cells to the right of the Active Cell
intCellCount = Range(ActiveCell, "D1").Cells.Count

'Select the cell to the right of the active cell
ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Select

'Turn off events so you don't get into and endless loop
Application.EnableEvents = False
With ActiveCell
'Clear the contents of the subsequent DV cells
.Resize(RowSize:=1, ColumnSize:=intCellCount) _
.ClearContents
End With
'Alert the user that an item must be selected
MsgBox _
Title:="Notice", _
Prompt:="You must now select an item from the next list", _
Buttons:=vbInformation + vbOKOnly
Application.SendKeys ("%{DOWN}")

End If

ErrTrap:
Application.EnableEvents = True
End Sub
'--------end of code----------

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

I could be wrong, but I don't think you need to clear the Function cell when
you change the Position cell, if the fields are in sequential order.....

meaning you choose in this order:
1)Select an Asset
2)Select a Function
3)Select a Position

Example using:
Col C Col D Col E
Asset Function Position
Hillsboro Cleaning Day Porter/Matron

You decide you don't want the Day Porter to do cleaning so you erase that
cell.
Do you really want the Cleaning function to erase, too?
Or do you want to leave it there so you can assign somebody else.

However, if you change the Cleaning function to Dog Walking, you'd probably
want the Position to erase.

And...if you change the Asset field....then Function AND Position should
erase.

NOW.....if the fields are NOT in sequential order...why aren't they? <bg

(either way I've got the code for the two scenarios ready to post..Just let
me know which way to go with it)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Toni" wrote:

Sorry if I was unclear.

C is the parent of D and D is the parent of E. If, after items are selected
for C, D, and E and the user deletes the item in Col C, I would like Cols D
and E to be blank. If the user deletes a selected item in Col E, I would
like Col D to be blank.

Col C Col D Col E
Asset Function Position
Hillsboro Cleaning Day Porter/Matron
Hillsboro Audiovisual Audiovisual Technician

Sorry for the confusion. Thank you for your patience.

Toni


"Ron Coderre" wrote:

I want to make sure that what you're asking for is what you really need.
(I'll assume all of the DV lists are on Row_2)

You indicated that a change to either D2 or E2 causes a change in C2, the
parent list, but no other changes.

Typically, in the scenario you described.....
whe
C2 is the parent DV list cell
D2 is dependent on C2
E2 is dependent on D2

then:
changing E2 has no impact on C2 and D2
changing D2 changes E2, but not C2
changing C2 changes both D2 and E2

Could you restate the request, with plenty of details, so can receive a
better quality response?

***********
Regards,
Ron

XL2002, WinXP


"Toni" wrote:

This worked for me as well. Thank you very much. Now I have a related
question

My ParentList is in col C. Col D is dependent upon C. Col E is dependent on
D. How can I modify the code so that when some deletes data from Col D or E,
Col C Parent List goes blank?

Thanks for your help.

Toni



"Ron Coderre" wrote:

You'd need VBA code to effect what you want.

Try working with this and post back with any questions

Assumptions:
Parent List cells are in A1:A10
Dependent List cells are in B10

Copy the below VBA code into the sheet module of the worksheet with the Data
Validation.

You get there this way:
Right-Click on the sheet tab
Select "View Code"

'----------Start of Code------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngAllParentCells As Range
Dim rngDepCells As Range
Dim rngCell As Range

Set rngAllParentCells = Range("A1:A10")
Set rngDepCells = Intersect(Target, rngAllParentCells)

If Not rngDepCells Is Nothing Then
For Each rngCell In rngDepCells.Cells
'Move 1 cell to the right and clear contents
rngCell.Offset(RowOffset:=0, ColumnOffset:=1).ClearContents
Next rngCell
End If

Set rngAllParentCells = Nothing
Set rngDepCells = Nothing
Set rngCell = Nothing

End Sub
'----------End of Code------------

Now....whenever one of the A1:A10 DV cells is changed,
the corresponding B1:B10 cell is cleared

Does that help?

***********
Regards,
Ron

XL2002, WinXP


"Shelly" wrote:

I used Ron's formula below, with success, but I would like something little
different.

I would like the B1 cell (dependent list) to be blank if the A1 cell is
changed after the B1 cell has been completed.

So, a user enters a value in A1, the list in B1 is restricted. They select
something from B1. They they go back to A1 and either change the value or
delete the value - at this point I would like B1 to go blank.

Any ideas?

THANKS