Here's the situation
Cell B3 has a data validation pulldown from a list of items
Cell C17 has a data validation pulldown that is dependent on the selected
item in B3
Problem: When an item is selected in B3 - c17 populates correctly.
Then when you change the Item in B3 - the new values are put into C17 - BUT
THE OLD Value remains being the selected item - until you manually use the
pulldown.
How do i automatically clear the old value?
I've tried. and the on change just gives me the error message of:"Could not
change dependent cell"

Your help would be greatly appreciated.
Sandy


Sub SetDropDown()
[C17].Value = Range([C17].Validation.Formula1).Cells(1, 1).Value
End Sub
============
in the on change subrountine adding
On Error GoTo errHandler
Dim rng As Range

If Not Intersect(Target, Me.Range("B3")) Is Nothing Then
If Target.Count 1 Then Exit Sub
Application.EnableEvents = False
Set rng = activeworksheet.Names(Target.Value).RefersToRange
[B3].Value = Range([B3].Validation.Formula1).Cells(1, 1).Value
Me.Range("C17").Value = rng.Offset(0, 0).Value
Me.Range("C17").Value = ""
Me.Range("C38", "C18").Value = ""
Me.Range("C39", "C42").Value = ""
Me.Range("C43", "C44").Value = ""
End If

texitHandler:
Application.EnableEvents = True
Exit Sub

errHandler:
MsgBox "Could not change dependent cell"
GoTo exitHandler

Hi,
Try removing the error handler to see which line of code triggers the error.

Also, maybe a simpler approach by just clearing one dependent cell. Start
simple then elaborate to clear other dependencies.

'------------------------------------------------------
Private Const AddrDropDown1 As String = "A1"
Private Const AddrDropDown2 As String = "A2"
'add other dropdown location here if necessary

Private Sub Worksheet_Change(ByVal Target As Range)
'Is it drop down one?
If Not Application.Intersect(Target, Range(AddrDropDown1)) Is Nothing Then
Application.EnableEvents = False
Range(AddrDropDown2) = ""
Application.EnableEvents = True
else
end if
End Sub
'------------------------------------------
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Sandy Ryan" wrote:

Here's the situation
Cell B3 has a data validation pulldown from a list of items
Cell C17 has a data validation pulldown that is dependent on the selected
item in B3
Problem: When an item is selected in B3 - c17 populates correctly.
Then when you change the Item in B3 - the new values are put into C17 - BUT
THE OLD Value remains being the selected item - until you manually use the
pulldown.
How do i automatically clear the old value?
I've tried. and the on change just gives me the error message of:"Could not
change dependent cell"

Your help would be greatly appreciated.
Sandy


Sub SetDropDown()
[C17].Value = Range([C17].Validation.Formula1).Cells(1, 1).Value
End Sub
============
in the on change subrountine adding
On Error GoTo errHandler
Dim rng As Range

If Not Intersect(Target, Me.Range("B3")) Is Nothing Then
If Target.Count 1 Then Exit Sub
Application.EnableEvents = False
Set rng = activeworksheet.Names(Target.Value).RefersToRange
[B3].Value = Range([B3].Validation.Formula1).Cells(1, 1).Value
Me.Range("C17").Value = rng.Offset(0, 0).Value
Me.Range("C17").Value = ""
Me.Range("C38", "C18").Value = ""
Me.Range("C39", "C42").Value = ""
Me.Range("C43", "C44").Value = ""
End If

texitHandler:
Application.EnableEvents = True
Exit Sub

errHandler:
MsgBox "Could not change dependent cell"
GoTo exitHandler