Does anyone know what event is associated with a change in a
validation list box? I have a row of about 20 "parent" validation
list boxes and two rows of about 20 "child" validation list boxes
(whose criteria change based upon the parent's). These cells
automatically set themselves to certain values, but the user needs to
be able to adjust them. I am trying to determine how to at least
blank out the child lists if the parent associated with it is changed.

For instance, If one box lists states and another lists cities, and
California is in the parent box and Los Angeles is in the child, how
do i keep the child from staying on Los Angeles when the parent is
changed to Oklahoma? This is a rather critical operation in my
code.

Thanks for your help!

Brian

On Apr 2, 1:06 pm, wrote:
Does anyone know what event is associated with a change in a
validation list box? I have a row of about 20 "parent" validation
list boxes and two rows of about 20 "child" validation list boxes
(whose criteria change based upon the parent's). These cells
automatically set themselves to certain values, but the user needs to
be able to adjust them. I am trying to determine how to at least
blank out the child lists if the parent associated with it is changed.

For instance, If one box lists states and another lists cities, and
California is in the parent box and Los Angeles is in the child, how
do i keep the child from staying on Los Angeles when the parent is
changed to Oklahoma? This is a rather critical operation in my
code.

Thanks for your help!

Brian

Brian,
Put this in your worksheet code. Change $I$14 to the cell that
validates the states.
If it's a range, you can set that to a range as well.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' This is the cell that validates the states
If Target.Address < "$I$14" Then Exit Sub
Target.Offset(0, 1).Value = ""
End Sub

Rob

On Apr 2, 1:51 pm, "okrob" wrote:
On Apr 2, 1:06 pm, wrote:





Does anyone know what event is associated with a change in a
validation list box? I have a row of about 20 "parent" validation
list boxes and two rows of about 20 "child" validation list boxes
(whose criteria change based upon the parent's). These cells
automatically set themselves to certain values, but the user needs to
be able to adjust them. I am trying to determine how to at least
blank out the child lists if the parent associated with it is changed.

For instance, If one box lists states and another lists cities, and
California is in the parent box and Los Angeles is in the child, how
do i keep the child from staying on Los Angeles when the parent is
changed to Oklahoma? This is a rather critical operation in my
code.

Thanks for your help!

Brian

Brian,
Put this in your worksheet code. Change $I$14 to the cell that
validates the states.
If it's a range, you can set that to a range as well.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' This is the cell that validates the states
If Target.Address < "$I$14" Then Exit Sub
Target.Offset(0, 1).Value = ""
End Sub

Rob- Hide quoted text -

- Show quoted text -

OK, I shoulda tested it... It didn't work as a range for me. It only
worked on one cell and it's offset.

On Apr 2, 1:51 pm, "okrob" wrote:
On Apr 2, 1:06 pm, wrote:





Does anyone know what event is associated with a change in a
validation list box? I have a row of about 20 "parent" validation
list boxes and two rows of about 20 "child" validation list boxes
(whose criteria change based upon the parent's). These cells
automatically set themselves to certain values, but the user needs to
be able to adjust them. I am trying to determine how to at least
blank out the child lists if the parent associated with it is changed.

For instance, If one box lists states and another lists cities, and
California is in the parent box and Los Angeles is in the child, how
do i keep the child from staying on Los Angeles when the parent is
changed to Oklahoma? This is a rather critical operation in my
code.

Thanks for your help!

Brian

Brian,
Put this in your worksheet code. Change $I$14 to the cell that
validates the states.
If it's a range, you can set that to a range as well.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' This is the cell that validates the states
If Target.Address < "$I$14" Then Exit Sub
Target.Offset(0, 1).Value = ""
End Sub

Rob- Hide quoted text -

- Show quoted text -


This works on a complete range:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_Rng As String = "I1:I20" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_Rng)) Is Nothing Then
With Target
Target.Offset(0, 1).Value = ""
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub