Search code examples
excellistvalidationdrop-down-menudropdownbox

Automatically reset drop down list to display first line (option)

hoping you can assist. Firstly, Pls forgive my lack of understanding as I’m not a programmer and have no real knowledge of VBA. What I am looking for is a piece of code that will reset a drop down list to display the first line (either a piece of code to reset a particular data validation list or a code that does so for all validation lists on a single sheet). To give context, I have been tinkering around with excel upskilling with various functions and formulas. As such, I’ve gone ahead and built a makeshift order/cash payment system for a coffee shop. I have all my data and respective ranges sitting in one sheet and the actual UI in another. In the UI sheet, I’ve created a few validation lists e.g under category coffee, I’ve created a drop down with “cappuccino, date mocha, latte etc” and for pastries a list with cakes, muffins etc. I’ve done this similarly for various other categories and the idea is when I choose from the lists to place the order, A vlookup and hlookup kicks-in to provide me with a price for either a short, tall or grande beverage, multiplied by the number of units. All this I’ve automatically built into a makeshift payment system that incorporates “total due, gratuity, payment, change etc”. At this point is where I get stuck, I’d like to have a button with an assigned macro that clears all lists back to first line once a transaction has been completed. NB: my first line is already built into the list as “Please select...” when either list reflects “Please select...”, all my totals are zero. So, the idea is to reset order list for i.e coffee, muffin etc, whether it’s short, tall or grande and the quantity all back to displaying first line. Can you please help. Ps: Not sure if this helps but my sheet with all the data is named Store Data and the UI sheet which hosts all drop down lists is named Store Compute. One last thing, all my validation lists are not named. Thanking you in advance and I do apologize for the lengthy query. Oh one last thing, would help if the code can be a simple copy and paste into a new VBA module ;-) thanks

Solution

  • This is a sample that you can adapt to your needs.

    I have a DV drop-down installed in cell A1 that refers to a list in column G:

    enter image description here

    Note that the first item in the list is Please Select..., (it can be anywhere in the G-list)

    We have similar drop-downs in A2 through A10. (these drop-downs can either also refer to column G or any other column-list)

    If you are using more than one column-list, make sure each list includes Please Select...

    Once you have made your selections and processed the data, run this tiny macro:

    Public Sub resetDVs()
    [A1:A10] = "Please Select..."
End Sub

    It will restore each of the cells to the desired "starting state" without having to click each one.

    EDIT#1:

    To have the code get the "reset" value from the G-list rather than the internal value, use:

    Public Sub resetDVs()
    [A1:A10] = Range("G1").Value
End Sub