Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
not clearing second pulldown
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clearing cells without clearing formulas | Excel Discussion (Misc queries) | |||
Pulldown that searches? | Excel Discussion (Misc queries) | |||
Autofilter Pulldown | Excel Programming | |||
pulldown boxes | Excel Programming | |||
pulldown boxes | Excel Programming |