ATTN : Ron (Multiple Dependent DV List Auto Update)

[Rajat]

Hi Ron

you gave me this code to Update Dependent DV List automatically i.e.
Cell A1 and B1 having DV through Data>Validation>List

When Cell Value of A1 Change following code clear cell B1 content and popup
a Msg Box to select value from the list

'--------that code is ----------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngParentCell As Range
Dim rngDepCell As Range
Dim rngCell As Range

Set rngParentCell = Range("A1")
Set rngDepCell = Intersect(Target, rngParentCell)

If Not rngDepCell Is Nothing Then
Set rngCell = Range("A2")
rngCell.ClearContents
rngCell.Select
MsgBox _
Title:="Notice", _
Prompt:="You must now select an item from cell A2", _
Buttons:=vbInformation + vbOKOnly
Application.SendKeys ("%{DOWN}")

End If

Set rngParentCell = Nothing
Set rngDepCell = Nothing
Set rngCell = Nothing

End Sub
'--------end of code----------

Now my problem is 1 have 3 Dependent DV on the sheet i.e.
on cell A1 value dependent is DV list of B1, DV list of C1 and DV list of D1

from the above code When A1 value change Msg Box popup and cell Ba selected,
but i need when value A1 change msg box appear and cell B1 selected and after
selecting value in B1 again msg box pop up and C1 selected and after
selecting value in C1 again msg box appear and cell D1 selected.

Thans in advance,

 

[Ron Coderre]

Hi, Rajat...sorry for the delay
(When your referenced "Ron", I didn't realize that you meant me)

The rules (as I understand them)
-You have 4 Data Validations (A11)
-When one value is changed
---you want the DV cells to the right of it cleared
AND
---you want the user prompted to select items from the subsequent DV lists

If that's true....Try this vba code:

'--------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("A1:C1")) Is Nothing Then

'Count the DV cells to the right of the Active Cell
intCellCount = Range(ActiveCell, "C1").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:
'Turn events back on...so Excel can behave normally
Application.EnableEvents = True
End Sub
'--------end of code----------

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

XL2002, WinXP