CODEWITHSUNDEEP
exceldrop-down-menudefault-valuevba
Markus Sacramento
Markus Sacramento

Reputation: 364

Excel VBA Dropdown list, default value

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

  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

Upvotes: 3

Views: 7396

Answers (2)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

Insert the default when you apply the 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"

Upvotes: 2

Andy G
Andy G

Reputation: 19367

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".

(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.)

Upvotes: 0

Related Questions