+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 18

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 Guru 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 Guru 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 Guru 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 2000/3/7/10/13/16
    Posts
    48,847

    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 2000/3/7/10/13/16
    Posts
    48,847

    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
    Tennessee
    MS-Off Ver
    2007
    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!

+ Reply to Thread
Page 1 of 2 1 2 LastLast

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