Setting default values ​​in the Data Validation drop-down list

Question

I have many data validation (DV) cells with a drop-down list that I configured using the menu Data > Data Validation > list . Is there a mass way in VBA to set all defaults? By default, I mean either:

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

The second option can be useful if we want the user to be responsible for his choice. As soon as the user clicks on the data verification cell, he is forced to choose something. There is no way to leave the default value. Choose item from the list because this value is not in the check list. Therefore, the user cannot later say "I did not vote."

Answer 1

This is what I'm ending.

 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 

HasValidation feature stolen from here .

After running the DropDownListToDefault macro, you will have the following choices after clicking in the DV cell:

Please note that there is no such item in the drop-down list as - Select from the list - If you want the user to select something from the drop-down list, just do not accept the default value during further processing.

Answer 2

To use the 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... ) using Name: e.g. validation and Refers To: will be your formula:

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

  • users with English, use , instead ; as a list separator

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

When you select a cell in the second row and observe the Name Manager , you will notice that the formula uses relative references to the current row.

---

To fill the cells with the default value, you can use the following formula (as a normal formula inside a cell, it has nothing to do with the data validation function):

 =INDEX(validation, 1) 

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