Hi,
I have a dropdown menu that gets it's options from a dynamic source. How do I set it so that when the selected is deleted from the source, the dropdown menu is reset to blank? Or preferably to a message such as "Please select..."
Thanks,
Anthony Bane
Let's say your dynamic values that you are pulling from are in H17:H21 and your dropdown list is in I16
Right click on the sheet tab>View code and paste this in
If you change anything in H17:H21, I16 clears.Formula:
Please Login or Register to view this content.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Hi ChemistB,
Thanks again for responding. How would I make this applicable to all dropdowns that I want, relative to their individual dynamic lists? In this instance the list size is always the same and so nothing to worry about, but the principle behind it is more important.
Thank you,
Anthony Bane
Let's say you have a set of DD from I16:I20 dependent on H17:H21 and a second set of DD in J16:J20 dependent on K17:K21
Please Login or Register to view this content.
So what you are saying is that I must create a new segment for every single drop down that I create and that I cannot create a function that can be inserted into the datavalidation settings? This seems like such a clunky way of doing things if you have more than just a few drop downs. It means that I cannot copy/paste a template and must then recode it should a line be added or removed. Or does Excel automatically update?
I am sorry for the questions but as I said, I am new to this.
Last edited by ABane; 05-14-2014 at 12:53 PM.
Only if each dropdown is unique to it's own range. I have 10 dropdowns covered in the code listed above.
The code I am now working with:
This works well for resetting the dropdown. However it resets after ANY change even if the selected menu option still exists. Is there a way of resetting ONLY if the option selected becomes invalid?Please Login or Register to view this content.
Do you have a sample workbook that you can share with us?
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
It will require much more code to become generic but you may use:
Please Login or Register to view this content.
- Please remember to mark threads Solved with Thread Tools link at top of page.
- Please use code tags when posting code: [code]Place your code here[/code]
- Please read Forum Rules
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks