Excel数据验证下拉箭头点击后无法向单元格填入数据问题咨询

Hey there, let's work through these two issues with your auto-complete data validation list step by step! I’ve run into similar problems before, so here’s how to fix them:

Issue 1: Dynamic List Only Updates When You Leave the Cell #

This happens because most default setups rely solely on the Worksheet_Change event, which only triggers once you finish editing and move away from the cell. To get real-time updates as you type, we need to add an extra event listener and adjust the logic:

1. Open the VBA Editor (press Alt + F11), then locate the worksheet module where your data validation is set up.
2. Replace or add this code snippet—remember to tweak the target cell range and source data range to match your workbook:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' Target the column with your data validation (e.g., column A)
    If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
        ' Define your source data range (e.g., Sheet2 column A, starting at row 2)
        Dim sourceRange As Range
        Set sourceRange = ThisWorkbook.Sheets("Sheet2").Range("A2:A" & ThisWorkbook.Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row)
        
        UpdateDynamicList Target, sourceRange
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
        Dim sourceRange As Range
        Set sourceRange = ThisWorkbook.Sheets("Sheet2").Range("A2:A" & ThisWorkbook.Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row)
        
        UpdateDynamicList Target, sourceRange
    End If
End Sub

Private Sub UpdateDynamicList(ByVal targetCell As Range, ByVal sourceData As Range)
    Dim filteredList As Collection
    Set filteredList = New Collection
    
    Dim inputText As String
    inputText = LCase(targetCell.Value)
    
    ' Filter source data for matches (case-insensitive)
    Dim cell As Range
    For Each cell In sourceData
        If LCase(cell.Value) Like "*" & inputText & "*" Then
            On Error Resume Next
            filteredList.Add cell.Value, Key:=CStr(cell.Value) ' Avoid duplicate entries
            On Error GoTo 0
        End If
    Next cell
    
    ' Convert filtered list to an array for data validation
    Dim resultArr() As String
    ReDim resultArr(1 To filteredList.Count)
    Dim i As Integer
    For i = 1 To filteredList.Count
        resultArr(i) = filteredList(i)
    Next i
    
    ' Refresh data validation with the new filtered list
    With targetCell.Validation
        .Delete
        If filteredList.Count > 0 Then
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(resultArr, ",")
            .IgnoreBlank = True
            .InCellDropdown = True ' Critical for proper dropdown functionality
        End If
    End With
End Sub

• The Worksheet_SelectionChange event initializes the list when you click into the cell, and Worksheet_Change updates it in real time as you type.
• The UpdateDynamicList function handles filtering matches, removing duplicates, and refreshing the data validation dropdown automatically.

Issue 2: Selected Dropdown Items Won’t Fill the Cell #

This usually comes down to two simple fixes:

1. Verify the "In-Cell Dropdown" Setting #

Right-click your target cell → Data Validation → Go to the Settings tab. Make sure the "Provide dropdown arrow" box is checked. This is controlled by .InCellDropdown = True in the VBA code above—if that line was missing or set to False, the dropdown won’t behave as expected.

2. Fix Data Source Problems #

• No merged cells: Merged cells in your source data or target cell will break data validation’s ability to write selected values. Unmerge any merged cells in the range.
• Clean up formatting: If your source data has extra spaces or weird formatting, matches might not register. Use the TRIM() function to clean up leading/trailing spaces in your source data.

Quick Debug Tip #

If things still aren’t working, open the VBA Editor’s Immediate Window (press Ctrl + G) to check for error messages. Add this error handling to the UpdateDynamicList sub to catch issues easily:

Private Sub UpdateDynamicList(ByVal targetCell As Range, ByVal sourceData As Range)
    On Error GoTo ErrorHandler
    ' ... existing code ...
    
ErrorHandler:
    If Err.Number <> 0 Then
        MsgBox "Oops, something went wrong: " & Err.Description, vbExclamation
        Err.Clear
    End If
End Sub

Test It Out #

1. Type a partial word into your target cell—you should see the dropdown list update immediately with matching entries.
2. Click the dropdown arrow, select an item, and it should populate the cell right away.

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