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:

the first value declared in a DV list
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".

Do you mean have the value in B1 SET the default value for all the drop downs? — peege, Jan 16 '15 at 10:50
default values are possible with VBA as well as with a formula. there is no `eval` built-in function though, so you cannot write a text of a function in a cell and run it somewhere else, however you can specify a named range using the `OFFSET` and use `=my_named_range` in data validation, would that be sufficient? — Aprillion, Jan 16 '15 at 11:32
@peege No. The default value is in C1. In D1, E1, F1, etc there are other values that a user may chose from drop-down list (which is in A1). C1 is a first value of array. — Przemyslaw Remin, Jan 16 '15 at 11:39
@Aprillion Defining named ranges would be absolutely not convenient because there is too many drop-down lists. Only making - in column A a drop-down list, in column B a formula with OFFSET, and in columns from C to Z item values - would be a good facilitation for me. — Przemyslaw Remin, Jan 16 '15 at 11:45
named ranges use relative references in the same way as normal formulas, if it is always the same `OFFSET` from `C` to `Z` in the current row, that can be achieved by 1 named range — Aprillion, Jan 16 '15 at 11:50

Aprillion · Answer 1 · 2015-01-19T11:49:44.170

3

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.

edited Jan 19 '15 at 11:49

answered Jan 16 '15 at 11:57

Aprillion

21,510
5
55
89

Thank you for that. What about setting all the drop-down lists to default values? Every value in A column should take values from C column. – Przemyslaw Remin Jan 16 '15 at 12:16
that is already answered in my answer - do you have any problems implementing the suggested solution? – Aprillion Jan 16 '15 at 12:18
It works if you set up validation as '=validation'. What is the reason to use '=INDEX(validation, 1)' ? – Przemyslaw Remin Jan 19 '15 at 09:36
1

none for the validation itself. only if you want to select the 1st value as the default in the actual cell's formula – Aprillion Jan 19 '15 at 10:32
Do you mean that I should put `=INDEX(validation, 1)` in drop-down validation source? Then it points only to the first cells value, and the drop-down lists consists of only the first item. I think that the formula itself also should be different since my items go in one row from columns C to Z (something like `=INDEX(validation, 1, 1)` . Nevertheless, it wouldn't help. But maybe, and I hope so, you mean some magic way of restarting _all_ validation ranges (I know there is only one) so that it points to first item. – Przemyslaw Remin Jan 19 '15 at 11:09
nothing to do with validation. it is a normal formula. just type it into formula bar. – Aprillion Jan 19 '15 at 11:46

Przemyslaw Remin · Accepted Answer · 2019-03-07T16:14:50.443

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.

Setting default values in Data Validation drop-down list

2 Answers2

Linked