Excel VBA下拉列表，默认值

Using following code to create dropdown if A Col has a value. How do I set No as default value?

如果A Col具有值，则使用以下代码创建下拉列表。如何将No设置为默认值？

  Dim myList As String, r As Range

  myList = "Yes,No"

  If w1.Range("A" & Rows.Count).End(xlUp).Address <> "$A$1" Then
    For Each r In w1.Range("A2", w1.Range("A" & Rows.Count).End(xlUp))
        If r.Value <> vbNullString Then
            With r.Offset(, 2).Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=myList
            End With
            If r.Offset(, 2).Value = "" Then r.Offset(, 2).Value = "No"
            If r.Offset(, 2).Value = "" Then Split myList, ","
            End If
      Next r
  End If

2 个解决方案

#1

Insert the default when you apply the DV:

应用DV时插入默认值：

replace:

更换：

With r.Offset(, 2).Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=myList
End With

with:

有：

With r.Offset(, 2).Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=myList
End With
r.Offset(, 2).Value = "No"

#2

A Validation drop-down does not have a default. You can fill empty cells with "No" and use "No,Yes" for your list.

验证下拉列表没有默认值。您可以使用“否”填充空单元格，并使用“否，是”作为列表。

Excel, unlike a database, doesn't require (by default) initiating a new record/row, so a default does not make sense to Excel. Given this, just filling all blank cells with "No" is not helpful, because you wouldn't know whether the user knows, or intended, to select "No".

与数据库不同，Excel不需要（默认情况下）启动新记录/行，因此默认值对Excel没有意义。鉴于此，只需用“否”填充所有空白单元格没有用，因为您不知道用户是否知道或打算选择“否”。

(With a database the user will physically see the field value assuming the default "No" value, and they then have the clear choice to accept it or change it.)

（对于数据库，用户将在物理上看到字段值，假设默认值为“No”，然后他们可以明确选择接受或更改它。）

#1