Clearing DropDown Lists & Removing External Links

[Ritchie Sobell]

I have two drop down lists (created using Data -> Validation -> List
and =INDIRECT() methods). This enables the second drop down list to
automatically contain corresponding info based on what is chosen in
the first list. (E.g.: If I picked Ford in the first drop down list
the second list will automatically contain all the different Ford
model vehicles).

What I want is, when selection changes in the first drop down list,
automatically the second drop down list should reset to blank. (I.e.:
After I picked Escort LX from the second drop down list, now when I go
to the first drop down and pick Honda, then the second drop down list
should automatically clear the Escort LX info from the previous choice
till I make a specific Honda model choice in the second drop down
list).

I believe writing code for the first drop down list will enable me to
do this. But, that is where I'm handicapped! Can someone please help
me with the code snippets and where the code should go? Currently I
have code associated with worksheet_change and
Worksheet_SelectionChange events.

On a different note, how do I remove all links associated with an
external Excel file from a workbook? I've tried Tools -> Options ->
Calculation Tab, cleared ‘update remote references'. But every time I
open the workbook, it prompts the following: ‘The workbook you opened
contains automatic links to information in another workbook. Do you
want to update this workbook…'. Originally this workbook had links but
the current version does not need any of the old links.

Any help would be appreciated.

Thanks.

 

[Tom Ogilvy]

You would add code in your WorkSheet_Change event to clear the cell
containing the second dropdown if the cell containing the first dropdown is
the target firing the Change Event

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' existing code

if Target.Address = "$E$5" then
application.EnableEvents = False
Target.offset(0,1).clearContents
Application.EnableEvents = True
End If
End Sub


down load Bill Manville's free findlink.xla utility.

http://www.bmsltd.ie

this is Stephen Bullen's site. From there go to the MVP page (links on the
left side).

 

[Ritchie Sobell]

Tom:
Thanks for your reply.

I forgot to mention that both dropdown lists are not individual cells,
instead each is a combination of merged cells. I tried your code and it
is erroring out saying that the contents of a merged cell cannot be
changed.

Any thoughts?

Thanks again.

 

[Tom Ogilvy]

Assume G5 is the upper left cell in the merged cells containing the second
dropdown.

if Target.Address = "$E$5" then
application.EnableEvents = False
Range("G5").Value = ""
Application.EnableEvents = True
End If
End Sub

or

if Target.Address = "$E$5" then
application.EnableEvents = False
Range("G5").MergeArea.clearContents
Application.EnableEvents = True
End If
End Sub