Data Validation List - Default Value

default_name

Board Regular
Joined
May 16, 2018
Messages
147
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
Hello,

I have been trying to find a solution to creating a Data Validation List that shows a default value (the top item in the list).
I found a solution on this board that sort of works.

Is it Possible for a Drop Down Menu to have Default Selections?
Post #3 seems to work....but only to a certain degree. 9I was contemplating commenting on the original post, but I figured I should create a new thread just in case)

However, included with the solution is a disclaimer which says
Keep in mind the formula is there and working until someone uses the DV list to make a selection, after that the formula is gone for that row, which is probably fine.

I am looking for a solution that challenges the disclaimer. If a selection is made from the DV list, is there a way to keep the cell from clearing the formula?
Or is there another method that will work?

My list can be found on a worksheet called Source in cells F6:F10
The value in cell F6 of the list is the word Standard.

Basically, I want the list to display Standard as the default value, unless the user changes it to something else.
But if they make this change, I do not want the formula to clear.

Thanks in advance for your help
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

t0ny84

Board Regular
Joined
Jul 6, 2020
Messages
117
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
Hi default_name,

I have used the following before with a Data Validation List of names, I added Select Name to the first option of the drop down list then added a button to the sheet. Whenever the sheet was finished with the user pushed the reset button and it would reset all drop down lists on the sheet to the first option - Select Name.

VBA Code:
Sub ResetDropDowns()
    
    Dim rngLists As Range
    Dim ListCell As Range
    
    On Error Resume Next
    Set rngLists = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo 0
    
    If Not rngLists Is Nothing Then
        For Each ListCell In rngLists.Cells
            ListCell.Value = Range(Trim(Mid(Replace(ListCell.Validation.Formula1, ":", String(99, " ")), 2, 99))).Value
        Next ListCell
    End If
    
End Sub


Or a macro to always set the value of the drop down list cell to "=IF(A2="", "", "Apple")" before the workbook is closed.

t0ny84
 

Watch MrExcel Video

Forum statistics

Threads
1,113,929
Messages
5,545,082
Members
410,652
Latest member
Zot
Top