+ Reply to Thread
Results 1 to 20 of 20

Resetting all dropdown menus to first item in list

  1. #1
    Registered User
    Join Date
    07-17-2012
    Location
    Nova Scotia
    MS-Off Ver
    Excel 2007
    Posts
    21

    Resetting all dropdown menus to first item in list

    Hello,

    I have a workbook with over 100 dropdown menu's on it. Almost all of the dropdowns populate the cell right next to them with data depending on what you choose.

    I would like to create a button that resets all of the dropdowns to the first item in the list. I've tried a few codes I found that I thought would work, but every code seems to just "clear" the menu.. So then the values populated by the drop down menu shows "#NAME?" instead of just being blank and the menu item selected is a blank space at the bottom of my list (I want it to select a blank space I put at the top of the menu). I know there are ways to make it seem like it worked like I wanted, but I need the menus to be set back to the first item.

    I also tried a few times to record a macro and manually chosing the first item in each list, but it didn't recognize my selections. Should I be able to record a macro to do this? If so, what could I be doing wrong?

    Any suggestions if recording isn't an option?

    Thanks!

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Resetting all dropdown menus to first item in list

    KJL,

    What kind of drop-downs are these? (data validation, form control, or activex control?)
    Do they all have the same first item? (a blank?)

    Also...
    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
    To attach a file, click the "Go Advanced" button and then click the paperclip icon to open the Manage Attachments dialog.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    07-17-2012
    Location
    Nova Scotia
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Resetting all dropdown menus to first item in list

    Thanks for the reply!

    Here is a copy of my workbook:

    [Removed]

    I basically want a button that will reset the workbook to more or less exactly how it is when you first open it. Most codes I've tried just puts a blank value into the dropdown, which selects the space below the list.
    Attached Files Attached Files
    Last edited by KJL; 07-25-2012 at 12:00 PM.

  4. #4
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Resetting all dropdown menus to first item in list

    KJL,

    Looks like you're using data validation for the drop-down lists. Here's a piece of code that will set all the drop-downs to their first entry:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-17-2012
    Location
    Nova Scotia
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Resetting all dropdown menus to first item in list

    Thank you so much, it worked perfectly!

  6. #6
    Registered User
    Join Date
    04-16-2013
    Location
    Topeka, ks
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Resetting all dropdown menus to first item in list

    Is there a way to reset data validation lists to their first entry without using code?

  7. #7
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Resetting all dropdown menus to first item in list

    Yeah, you could do it manually. There's no formula or built-in data-validation solution though.

  8. #8
    Registered User
    Join Date
    12-08-2014
    Location
    Martin
    MS-Off Ver
    14
    Posts
    1

    Re: Resetting all dropdown menus to first item in list

    Is there an option how to select more than one sheet in range?

  9. #9
    Registered User
    Join Date
    12-23-2016
    Location
    India
    MS-Off Ver
    2016
    Posts
    11

    Re: Resetting all dropdown menus to first item in list

    Quote Originally Posted by tigeravatar View Post
    KJL,

    Looks like you're using data validation for the drop-down lists. Here's a piece of code that will set all the drop-downs to their first entry:
    Please Login or Register  to view this content.
    Thanks! the above piece of code perfectly run for an individual sheet, however is there a way I enhance this to my entire workbook. I have multiple drop downlist, spread across multiple sheets.
    I would like to reset them all at once using this. Kindly suggest please.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Resetting all dropdown menus to first item in list

    Hawks, welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  11. #11
    Registered User
    Join Date
    12-23-2016
    Location
    India
    MS-Off Ver
    2016
    Posts
    11

    Re: Resetting all dropdown menus to first item in list

    Hi Ford,

    Thanks for the suggestion, can you please suggest how do I start my own thread.

    Regards
    Hawkz
    Last edited by hawkz; 12-24-2016 at 02:50 AM.

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Resetting all dropdown menus to first item in list

    At the top of the window, click FORUM, then select a forum (say, General), click Post new Thread (at the top), and you should be good to go

  13. #13
    Registered User
    Join Date
    12-23-2016
    Location
    India
    MS-Off Ver
    2016
    Posts
    11

    Re: Resetting all dropdown menus to first item in list

    Perfect, thanks just posted a new thread.

  14. #14
    Registered User
    Join Date
    10-31-2013
    Location
    Kuopio, FInland
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Resetting all dropdown menus to first item in list

    shorter expression works also in the loop:
    listcell.value=split(listcell.validation.formula1,",")(0)

    This works if the validation formula is the list (e.g. "first, second, third"). If the validation list is a range, then use this:

    if left(listcell.validation.formula1,1)="=" then
    listcell.value = range(mid(listcell.validation.formula1,2)).cells(1)
    else
    listcell.value=split(listcell.validation.formula1,",")(0)
    end if

    Best regards Otto
    Last edited by otto.hanninen; 05-19-2017 at 10:18 AM.

  15. #15
    Registered User
    Join Date
    01-02-2018
    Location
    Florida
    MS-Off Ver
    365
    Posts
    4

    Re: Resetting all dropdown menus to first item in list

    I have the same issue. Multiple drop downs that I need to clear. I have attempted to copy and paste the code given and retyping it but it does not work. This is my first attempt at using code in Excel so I am a bit lost. Do I use it exactly as provided or do the items such as Range, listcell, etc. have to be replaced with specific items from my workbook? My internet searches have only served to make me more confused. Help a code newbie please!

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Resetting all dropdown menus to first item in list

    SGArete welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

  17. #17
    Registered User
    Join Date
    09-23-2018
    Location
    washington, dc
    MS-Off Ver
    google sheets
    Posts
    1

    Re: Resetting all dropdown menus to first item in list

    Hi, I'm trying to do the same thing with a google docs sheet. I tried to copy the code that worked for you, but am getting an error. any suggestions would be greatly appreciated! I'm at a loss here

  18. #18
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Resetting all dropdown menus to first item in list

    sbrawner welcome to the forum

    Not sure how you managed it, but it appears that you missed my post directly above yours?

  19. #19
    Registered User
    Join Date
    05-10-2020
    Location
    egypt
    MS-Off Ver
    2010
    Posts
    2

    Re: Resetting all dropdown menus to first item in list

    How To rest drop down list after Go to Specific Worksheet Based On Drop-down Lists In Excel I need your help urgent .. I have already one formula in VBA to go to specific sheet but don't know how to make another formula to rest the drop down list and both of them work

  20. #20
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Resetting all dropdown menus to first item in list

    Quote Originally Posted by moudent View Post
    How To rest drop down list after Go to Specific Worksheet Based On Drop-down Lists In Excel I need your help urgent .. I have already one formula in VBA to go to specific sheet but don't know how to make another formula to rest the drop down list and both of them work
    It seems you also missed reading post #16?
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1