表单单元格自动补全技术问询：实现相邻单元格动态联动存储与补全

Hey there! Let's tackle this auto-complete problem you're facing with your form—sounds like the bottom storage list limitation is the main hurdle, but we can work around that with in-cell dynamic lists and some tweaked VBA (since your first macro attempt had space issues). Here are two solid solutions:

Solution 1: No-Macro Approach (Dynamic Data Validation) #

If you want to avoid macros entirely, we can use Excel's built-in dynamic ranges and data validation to pull off auto-complete without a hidden bottom list:

• Step 1: Pick a hidden storage column
  Choose a column that’s out of your workflow (e.g., the far-right column like Column X) to store all unique names. Since you can’t use the bottom of the sheet, a side column won’t interfere with your empty备注 cells.
• Step 2: Define a dynamic named range
  Go to the Formulas tab → Name Manager → New. Name it something like ExistingNames, then set the "Refers to" field to:

  =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
  

  (Adjust Sheet1!$A$2 to your first name cell, and $A:$A to your name column. This range automatically expands as you add new names.)
• Step 3: Set up data validation
  Select all your name input cells (e.g., A2:A100). Go to Data → Data Validation → Choose List as the type. For "Source", enter =ExistingNames, then check:
  • "Provide dropdown arrow"
  • "Allow input that is not in the list"
• Step 4: Enable memory-based auto-complete
  Make sure Excel’s built-in auto-complete is turned on: File → Options → Advanced → Edit options → Check "Enable AutoComplete for cell values".

Now, when you type a name that’s already in your list, Excel will auto-fill it. New names you enter will automatically get added to the dynamic range for future use.

Solution 2: Tweaked VBA Macro (Fixing Space Issues) #

Your original macro had space-related problems—let’s fix that by adding whitespace cleanup and unique name handling. This macro will auto-update the auto-complete list in the cell itself, no hidden storage list needed:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim NameColumn As Range
    Dim UniqueNames As Collection
    Dim Cell As Range
    Dim CleanName As String
    
    ' Adjust this to your name column (e.g., Column A = "A:A")
    Set NameColumn = Me.Range("A:A")
    
    ' Only run if a single name cell is edited
    If Target.Count > 1 Or Intersect(Target, NameColumn) Is Nothing Then Exit Sub
    
    ' Clean up leading/trailing spaces from the input
    CleanName = Trim(Target.Value)
    Target.Value = CleanName ' Write the cleaned name back to the cell
    
    ' Skip if the cell is empty
    If CleanName = "" Then Exit Sub
    
    ' Collect all unique, cleaned names from the column
    Set UniqueNames = New Collection
    On Error Resume Next ' Ignore duplicate key errors
    For Each Cell In NameColumn
        If Cell.Value <> "" And Cell.Row <> Target.Row Then
            UniqueNames.Add Trim(Cell.Value), Key:=UCase(Trim(Cell.Value))
        End If
    Next Cell
    On Error GoTo 0
    
    ' Add the new cleaned name to the collection
    On Error Resume Next
    UniqueNames.Add CleanName, Key:=UCase(CleanName)
    On Error GoTo 0
    
    ' Update the cell's validation list with all unique names
    With Target.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
             Formula1:=ConvertCollectionToList(UniqueNames)
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
    End With
End Sub

' Helper function to turn the collection into a comma-separated string
Function ConvertCollectionToList(col As Collection) As String
    Dim i As Integer
    Dim ListString As String
    
    For i = 1 To col.Count
        ListString = ListString & col(i) & ","
    Next i
    
    ' Remove the trailing comma
    If Len(ListString) > 0 Then
        ListString = Left(ListString, Len(ListString) - 1)
    End If
    
    ConvertCollectionToList = ListString
End Function

How to use this: #

1. Right-click your worksheet tab (e.g., "Sheet1") → View Code
2. Paste the code above, then adjust NameColumn to match your actual name column
3. Save your file as an .xlsm (macro-enabled workbook)

This macro cleans up any accidental spaces in names, stores only unique entries, and updates the auto-complete dropdown in real-time whenever you add a new name.

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