Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing DropDown Lists & Removing External Links
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing DropDown Lists & Removing External Links
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). -- Regards, Tom Ogilvy "Ritchie Sobell" wrote in message om... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing DropDown Lists & Removing External Links
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. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing DropDown Lists & Removing External Links
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 -- regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... 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). -- Regards, Tom Ogilvy "Ritchie Sobell" wrote in message om... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing External Links | Excel Worksheet Functions | |||
Clearing associated dropdown fields | Excel Worksheet Functions | |||
Clearing Links | Excel Discussion (Misc queries) | |||
Removing external links in Excel | Excel Programming | |||
Removing "external links" in Excel | Excel Programming |