Setting default values in Data Validation drop-down list

Question

I have many data validation (DV) cells with drop-down list which I have set up by menu Data > Data Validation > list. Is there a bulk way in VBA to set them all to default values? By default value I mean either:

1. the first value declared in a DV list
2. or value which is not on the DV list, something like Choose item from the list.

The second option might be useful if we want the user to be responsible for his choice. As soon as the user clicks on a data validation cell, he is forced to choose something. There is no option to leave default value Choose item from the list because such value is not on the validation list. So the user cannot say later "I didn't vote".

Answer 1

To use an offset from columns C to Z in the current row:

• select any cell in the first row

• create a named range (Formulas > Name Manager > New...) with Name: e.g. validation and Refers To: would be your formula:

  =OFFSET($C1;0;0;1;COUNTA($C1:$Z1))
  

  • english-locale users, use , instead of ; as the list separator

• select cells and apply Data Validation > Allow: List, Source: =validation

When you select a cell in 2nd row and observe the Name Manager, you will notice that the formula is using relative references to the current row.

---

To populate cells with the default value, you can use following formula (as a normal formula inside a cell, it does not have anything to do with the data validation feature whatsoever):

=INDEX(validation, 1)

and when you actually select a value from the drop-down list, the formula will be overwritten by the selected value, so when you change the 1st item in your list, the value will not change for explicitly selected cells.

Answer 2

This is what I end up with.

Sub DropDownListToDefault()
    Dim oCell As Range

    For Each oCell In ActiveSheet.UsedRange.Cells
        If HasValidation(oCell) Then
            oCell.Value = "'- Choose from the list -"
        End If
    Next
End Sub

Function HasValidation(cell As Range) As Boolean
    Dim t: t = Null

    On Error Resume Next
    t = cell.Validation.Type
    On Error GoTo 0

    HasValidation = Not IsNull(t)
End Function

The function HasValidation is stolen from here.

After running the macro DropDownListToDefault you will have the following choice after clicking in a DV cell:

Note that in the drop-down list there is no item like - Select from the list - If you want a user to choose something from the drop-down list simply do not accept the default value in further processing.