vba 在数据验证下拉列表中设置默认值

Setting default values in Data Validation drop-down list

提问by Przemyslaw Remin

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:

我有许多带有下拉列表的数据验证 (DV) 单元格，我已通过菜单Data> Data Validation>设置了这些单元格list。VBA 中是否有批量方法将它们全部设置为默认值？默认值我的意思是：

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.

1. DV 列表中声明的第一个值
2. 或不在 DV 列表中的值，例如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 listbecause such value is not on the validation list. So the user cannot say later "I didn't vote".

如果我们希望用户为他的选择负责，则第二个选项可能很有用。一旦用户点击数据验证单元格，他就会被迫选择一些东西。没有保留默认值的选项，Choose item from the list因为该值不在验证列表中。所以用户以后不能说“我没有投票”。

采纳答案by Przemyslaw Remin

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.

函数 HasValidation 是从这里窃取的。

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

运行宏DropDownListToDefault后，单击 DV 单元格后，您将有以下选择：

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.

请注意，在下拉列表中没有类似- Select from the list -如果您希望用户从下拉列表中选择某些内容的项目，请不要在进一步处理中接受默认值。

回答by Aprillion

To use an offset from columns Cto Zin the current row:

要使用从列C到Z当前行的偏移量：

• select any cell in the first row

• create a named range (Formulas> Name Manager> New...) with Name:e.g. validationand 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

• 选择第一行中的任何单元格

• 创建一个名为范围（Formulas> Name Manager> New...）与Name:如validation和Refers To:将是你的公式：

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

  • english-locale 用户，使用,而不是;作为列表分隔符

• 选择单元格并应用Data Validation>允许：List，来源：=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.

当您选择第 2 行中的一个单元格并观察 时Name Manager，您会注意到该公式正在使用对当前行的相对引用。

---

---

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.

并且当您从下拉列表中实际选择一个值时，公式将被所选值覆盖，因此当您更改列表中的第一项时，显式选择的单元格的值不会更改。