Reset Dependent Drop-down in Excel (with a tiny bit of VBA)
HTML-код
- Опубликовано: 12 дек 2018
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Learn how to reset a dependent drop-down list in Excel with a little bit of VBA. The problem we get with dependent data validation lists is that when you change the value of the first data validation, the value for the dependent data validation stays there until you activate the drop-down. This can be misleading. In this video, you will learn how to automatically reset the value of the dependent list to say "please select" the moment the value in the first drop-down list changes.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/reset-dropd...
We do this with Excel VBA, using the change event procedure. The default worksheet procedure is Selection Change event. This triggers every time you change the active cell. It's more optimal in this case to use the worksheet change procedure and make sure that it only run if the value of a specific cell is changed. We don't want to trigger the change procedure every time any cell value is changed.
The simple Excel macro show you how you can set it up from scratch. Once you're done, make sure you save the workbook as a macro-enabled workbook.
LINKS to related videos:
Dependent data validation in Excel: • Dependent Drop-Down Li...
Dependent Combo Box: • This Excel Dependent C...
VBA IF THEN Statement: • Excel VBA IF THEN Stat...
VBA Message Box: • How to use the Message...
Full VBA playlist on RUclips: • Excel VBA & Macros Tut...
★ My Online Excel Courses (including VBA) ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel #ExcelVBA
Grab the file I used in the video from here 👉 pages.xelplus.com/reset-dropdown-file
I'm flying through my assignments at work which will be making me look good, thanks to these tutorials. Thank you Leila!
Happy to hear that!
I know this video was published quite a while ago, but it came in handy today helping me clean up dynamic drop down list issues (annoying CALC! error) and setting up filters via those multiple selections. Thanks, again.
Hi Leila.. excellent method to keep the user from being confused when the master drop-down changes. Thanks and Thumbs up!
Thanks for the amazing high quality video, as always ; )
Ditto. Very high quality video, easy code addition for great result.
Thank you for your wonderful comment teammate :)
Your videos are are rich source of conceptual knowledge.
I spend hours looking threw different peoples solutions from a google search and NOTHING was just this simple. This worked perfectly, and with the smallest amount of code I have seen yet. THANKS!!!!!
I'm glad to hear that. Glad it worked out.
Awesome tutorial, saved my day. I have multiple dependent drop down. If one changes other subdependent cells should also change. So i have copied the code and pasted changing the target address and range adress. Kudos to you and your team 👍
I'm glad the video was helpful for you.
Thank You! This tutorial gave a solution to a problem which I had since my first dependent dropdown list.
Thank you so much for your wonderfully detailed yet easy to follow videos!! They have saved me so much time over the last few months. Have a wonderful Christmas and I look forward to more in 2019
That‘s amazing. Thank you so much. Your video gives a solution to the problem which I have faced already many times with dependent drop-down lists, when the values were not changing automatically.
That's great! I'm happy to hear that :)
Thanks for the high quality videos, they have helped me tremendously.
It would be fantastic if you could do some videos on creating a personal finance/budget spreadsheet. I'm sure a lot of people would highly regard it and learn a lot.
Thanks.
Never thought I would ever try out VBA but with your guidance it actually is much less threatening.
That's good to hear :)
Wow!! A very useful tips for us. Thank you so much ma'am. You are really good teacher.
Thank you for an awesome video and VBA fun! It is super cool, that validation doesn't validate VBA ;)
You are the best Leila!
I'm your big fan. This vlog is a very big help on the report that I am doing. 👍👍👍
Thank you very much for the great tutorial. This is exactly what I've been looking for!
Great to hear!
Perfectly explained as always.
Thanks Vida.
Really Thankful to you. You solved my big problem of excel.Again Thank You
Awesome, thank you.
Great Explained. Waiting what next tutorials brings.
Thank you Ashok. Looking forward to seeing you in the next ones too.
Thank you for another great lesson.
You're welcome Craig. Thanks for dropping by :)
Simply amazing, Thank you:)
Great video. Very helpful
Thanks! this was very helpful.
Thanks for the Amazing video, It is helping us in depth....
Big fan Leila! This was very helpful TY!
Glad it was helpful Richard!
Very much enjoyed!! Thank you!!
Glad you enjoyed it!
Thank you for this video.
Thanks Leila!
This is rock n roll stuff. Thanks
🤘
Great tutorial
Glad you like it.
For me it's changing value even on just moving the cursor to the Target cell, even before taking any action of changing the dropdown value. This is working fine in ur video though
You are awesome !
thanks was scouring th internet and your videos for this solution!!!
Glad I could help.
Thank you so much mam....😊👍👍
Excellent.
Thank you!!!
it was just a small mistake i was making which got sorted!!!!
Great! Glad it worked out.
All your videos are very helpful. Thank you so much.
Can you also give tutorial if the dropdown list multple like 4 dropdown?
Leila Thanks so much for making all the videos you do. You help so many people including me. I have a question related to DV lists and possible VB to click a button to reset a range of cells (that are DV lists) back to a specific choice. Any chance you can help me with that?
Thanks a lot
Hi Leila - love the videos. I learn a lot. How to I reset a dependent dropdown if the original dropdown is in another sheet?
Hi Leila..
Your way of teaching is awesome as always.. I have a small request from my end " Could you please Make a video on Web scraping using VBA "... I also searched your video on web scraping in udemy as well but didn't find
Thank You so much
You're so very welcome :)
sweet 10sec solution!
Thanks a ton!! 💥💥💥💥💥💥💥💥💥💥
Thank you so much mam.
You're very welcome. Thank you for your support.
Hi Leila,
Amazing video. Lately I have started following your videos and it has helped me on multiple occasions. Precisely what I needed.
Thanks for it!
Regarding this video, I applied the VBA logic on one row (no. 8), I have column "H" and "I" is dependent on H, further "J" is dependent on "I".
Could you please guide me on how to fill down this logic for the entire range of 50?
Thanks,
Sumit
Hi Leila ,I follow all your lessons. They are very useful and informative videos.
I need help in resetting dependent drop-down list for entire column.
Could you please share the related video link.
Thankyou sis.
Hi Leila. GREAT tutorial! I would like to ask you how - using a macro - can I initialize the values in a number of 10 combo-boxes (i.e. to the value "Not answered" for all of them)
The best.
Good evening Leila, I have recently stumbled onto your videos and I've found them rather useful, so thank you. I have a question for you, that I'm hoping you can assist. I am in the middle of creating a database to (mostly) auto populate dollar figures based on a number of criteria. The dollar figures are based on the every day of the week, including weekends and public holidays. I managed to use the date range to populate individual cells that essentially provide the tally of weekdays/weekends in the given date range. I also managed to calculate the holidays that fall within that specific date range (although the date range will change, as the year progresses). This is where some of the Excel formula has me a bit stumped.
It has me stumped because some of the public holidays in Australia fall on weekends and I can't seem to work out how to manipulate the formula that populates the individual cells that tally the weekdays/weekends whilst also recognising that a public holiday falls on one. It is a tad difficult to explain, so I hope the above is sufficient. The reason I'm seeking an answer to this is so I can, on another work sheet, input the relevant number of week days, weekend days and public holidays of a given date range, as the dollar calculations will be based on those.
Thanks in advance,
Wes.
Great Video and finally I found how to solve this. But instead of "Please select..." I would like to use first value in a new drop down list. How should I change the code in this case? Thanks!
Man i don't need a ux guy to design wireframes, if i learn these tricks i can create forms by myself completely in excel !!
Super 👍👍
Hi ma'am,
really awesome videos made by you...which help us to learn Excel in a easy ways...
please help me ...
i have 10 drop-down list containing items.
if i select item 1 from the first drop down.
in second drop down list item option should start with item 2 ,item 3, item 4 . and so on
How would this be possible... in Excel
Nice video mam
Thank you Ankur :) Glad you like it.
Amazing thank you! How can this apply to all lines in a table? this code only works on 1 cell. I also have several column with dependent drop down list that I would like to reset at the same time.
Nice, thanks
🤔 wonder it will work on table row by row,
Leila....thank you so much for all your tutorials.
I am having trouble with this one though.
It work great the first time I did it, but then I saved it(macro enabled file), now when I open the file again, it does not work.
HELP!!!
thank you mam. ...
You're very welcome sandeep.
Thank you for this video. I was trying this procedure but I didn't use Target.Address. So every time I was getting error 1004
Thank you Leila! Awesome solution, but Access forms has built in features for combo box like auto search or after update event that none programmer users can use it without any vba codes. VBA is very useful in MS ACCESS.
Please make some videos about Power BI desktop. thanks a lot.
Thank you for your feedback Ali. Yes - BI added to my list for next year's videos :)
Amazing tips, thank you so much.
How we can use it for multiple rows.
Let's say I have a data validation drop down at E4:E200, how to used VBA code for that.
could you please guide me on this?
Thank you & keep rocking with your valuable content.
Would you be able to apply this type of logic to a form button, linked to a data validation list, that resets it to the first value (static value) each time you click the button?
thanks..
Hi Leila! Great video!
How do I get the VBA reset function to work on several rows?
Thanks again!
I have the same question :)
Great video but what is the formula for VBA if you need to apply to an entire column not just two cells?
Thanks your videos helped a lot but ma'am I need help. I try taking the formula (from your previous video) and insert it into another sheet but it gives me an error. So my question is How do I take the formula (=OFFSET(F5,,,COUNTIF(F5:F14,"?*"))) and insert it into my source but in another sheet?
Great
Thank you so much for this. How would I set the original cell to also read "Please Select..." via VBA when the workbook is open?
Even i would like to know it :)
Thanks for the tutorial. Why do you need to use $ for $G$4 but not for G6? Thanks.
thank you so much for this tutorial. I have a question. if I want to make this VBA work for multiple dependent drop-down lists, what code should i add?
Exactly what I want! but apparently she doesn't have that
@@ronellll did you find the solution?
Could you please explain how to grey out and enable list validation in excel from Editing. Thanks
Mam, Great Video. But is there any VBA where we can change a drop down list based on only value (not data validation) in another cell? Helpful if you present this video.
Hey, does this interfere with copy and paste operations? I read somewhere that Worksheet_Change and similar events can break copy and paste?
Is that a formula to choose the final drop-down list instead of going through from the first drop down?
When chosen the second drop-down, the first will change to the consistent data as well. Thanks in advance
Hi Leila, can you advise if this works with earlier version of Excel? I have created and I find it doesn't work with 2007 Excel despite Macro has been enabled. Is there anything we can do to cater for all users?
Hi, great video. How can I apply the vba to multiple rows? I generally add my data to a row and then copy down hundreds if rows. Thanks
I'm having the same problem, did you already find a solution?
I inputted the code from her example as "G6:G10" and the text applied to the subsequent rows until G10.
Thank you very much Leila..... But configure same to all the rows. For example i have to create a table with this system. ? please help.........
did you find the solution?
Just discovered and LOVE your videos. Question - Do you have a video for "Resetting Dependent Drop-Down" in Google Sheets? Thanks!
Did you find a video or figure out a solution?
Hi, Leila! How are you? I love your channel. I'm learning a lot with your content. I'm wondering if you may help me with an issue in VBA.
My subroutine creates a new sheet and uses VBIDE Library to write an event SelectionChange in the sheet code. So, Creates Sheet>Insert SelectionChange code in it. The Code runs fine. The SelectionChange works great. However, after executing the sub, VBA opens automatically showing the sheet event code window and I don't know why and how to solve it. May you help me?
I solved partially the problem with Application.SendKeys "%{F4}" at the end of the subroutine. VBA doesn't stay open, but I see a flash when it is closing
Hi Leila, Thank you for your video. Can you please explain how to do the same thing when we have a ComboBox instead of Data Validation? Defining e.g. $G$4 doesn't work for when we have a ComboBox (DropDown1 for example) in our sheet.
A very simple way is to manipulate the cell links of the combo boxes. Right-mouse click on the first combo box and select "assign macro" - then click on New - you'll be taken to the change procedure. In this change procedure you can force the cell link value of the second combobox to become 1. So if the cell link of the 2nd combo box is in M4, you would type in Sheet2.Range("M4").Value = 1
As the first category you could have the word "please select..." in your list.
@@LeilaGharani How do I get the VBA reset function to work on several rows?
How do i change the ListFillRange in a ComboBox with a Macro by clicking on a option Button
Strange but this doesn’t work for me in my sheet. I downloaded the version on line and it worked fine until I changed the inputs to named ranges - is there something else I need to do? Thx
How this reset can be applied to all cells of a row??
Thanks, . . . . mam, can we jump from one sheet to another from drop down list??
Let's say drop down is in G2
If Range("G2"). Value = sheet2 then Sheets("Sheet2").activate
is there a similar training video for this function for GOOGLE SHEETS?
Your explnatin works fine for a 2 depaendant list, how to do it for 3 dependant lists?
how to reset multiple dropdownlist... thanks I'll wait for the next tutorial my dropdown list is up to 5,000 cell.
thanks
Hi did you find a solution yet? Please share. I have the same problem.
Thank you.so much i am a newbie at Visual Basic WHat if you want to apply the same logic for multiple cells since they all are dependent drop down lists
For Eg for change in (C1) D1 and E1 gets reset
for change in (C2) D2 and E2 gets reset..
....(C20) D20 and E20 get reset
Mam, I need answer for this, please support.
You can use elseif
Hi., Thanks for the great video.
But may i know how to apply to all rows?
Hi Siti, Did you got the solution of this issue. I am looking fro same solution
@@YoginderKrSingh any update on this?
@@193_rohan5 write me back on kumar.yogindersingh@gmail.com
I have the same problem. Did you find a solution yet?
Great tutolial, thanks! However, id doesn't work. What coudl be the reasons for that? I have a macro enabled excel and did all as per tutorial, but the dependent drop-down doesn't update.
I am having the same problem it's so annoying. Even the steps prior to that work with the cell reference appearing in a message pop up box. But this particular VBA code just doesn't execute even though it's exactly the same with the cell references updated. HELP! :(
I had the same issue, couldn't find why. Probably it's related to some kind of version differences.
How I solved this problem was recording a macro. My Excel didn't accept changing drop down list's value by referring to it such as Range("A2"). Value = blablabla. - assume A2 is the range where you drop down list located.
So I enabled macro recorder and wrote the value in drop down list's cell and checked how recorder writes the code. Then I changed the value as excel did and it worked.
I don't remember the code and I can't check it since I'm on mobile but you can check it on your own.
what if I need to write this for multiple cells?
Hi that's an amazing tutorial.
Is there any way to do the same in google sheet?
Did you find a video or figure out a solution?
can you help me i need to do this with multi columns not just on cell plz help me
Hi did you get the solution yet? If so, please share it with me? I am stuck...... Thanks
How to change Dropdown List font Size?
Ma'am Today I have question That I want Formating in the way if I put
123 in A1 cell then B1 cell give me ABC, just like that if i put 546(could be any number) in A2 cell then B2 give me EDF(It could be any thing according to given numeric in A2 cell) ,So on. Ma'am Want it By Formatting or By formula but short Formula, Pls Ma'am Make it Possible. Your "Excel Fan".