Data Validation List - Default Value

[default_name]

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

 

[t0ny84]

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.

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