Excel VBA：A列有值时创建下拉列表，如何设默认值为No？

Got it, let's walk through how to solve this! Whether you want to run the macro manually or have it trigger automatically when values are added to column A, here's what you need to do:

Option 1: Manual Macro (Run When Needed) #

This macro will scan column A, and for every cell with a value, it adds a dropdown list (with options like "Yes" and "No") in the adjacent column B, setting "No" as the default selected value.

Sub AddDropdownWithDefaultNo()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim cell As Range
    
    ' Target your worksheet (update "Sheet1" to your actual sheet name)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Find the last row with data in column A to avoid unnecessary loops
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each cell in column A
    For Each cell In ws.Range("A1:A" & lastRow)
        ' Only act if the cell isn't empty
        If cell.Value <> "" Then
            ' Choose where to place the dropdown (here, column B next to the A cell)
            Dim dropdownCell As Range
            Set dropdownCell = cell.Offset(0, 1)
            
            ' Clear any existing validation to prevent errors
            dropdownCell.Validation.Delete
            
            ' Create the dropdown list with your desired options
            With dropdownCell.Validation
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                     Operator:=xlBetween, Formula1:="Yes,No"
                .IgnoreBlank = True
                .InCellDropdown = True
            End With
            
            ' Set the default value to "No"
            dropdownCell.Value = "No"
        End If
    Next cell
End Sub

Quick Notes for This Macro: #

• Change the worksheet name: Replace "Sheet1" with the name of your target sheet.
• Adjust dropdown location: If you want the dropdown in the same cell as column A, swap Set dropdownCell = cell.Offset(0, 1) with Set dropdownCell = cell.
• Add more options: Modify Formula1:="Yes,No" to include additional choices (e.g., "Yes,No,Maybe").

Option 2: Auto-Trigger When Column A is Updated #

If you want the dropdown to appear automatically as soon as someone enters a value in column A, use this worksheet event. It will run in the background whenever a change is made to column A.

1. Right-click your worksheet tab (e.g., "Sheet1") and select View Code.
2. Paste this code into the module that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim affectedCell As Range
    
    ' Check if the changed cell is in column A
    If Not Intersect(Target, Me.Columns("A")) Is Nothing Then
        ' Loop through all cells that were changed (in case of bulk pastes)
        For Each affectedCell In Intersect(Target, Me.Columns("A"))
            ' Only add the dropdown if the cell has a value
            If affectedCell.Value <> "" Then
                Dim dropdownCell As Range
                Set dropdownCell = affectedCell.Offset(0, 1)
                
                dropdownCell.Validation.Delete
                With dropdownCell.Validation
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                         Operator:=xlBetween, Formula1:="Yes,No"
                    .IgnoreBlank = True
                    .InCellDropdown = True
                End With
                
                ' Set default to "No"
                dropdownCell.Value = "No"
            End If
        Next affectedCell
    End If
End Sub

This way, you don't have to remember to run the macro manually—every time a value is added to column A, the dropdown with default "No" pops up right away.

内容的提问来源于stack exchange，提问作者Markus Sacramento