如何通过Excel用户窗体的列表框编辑更新数据库中的现有记录？

解决方案 #

核心逻辑是在列表框中新增一列隐藏的源数据行号，双击选中记录时记录该行号，更新时直接定位到原工作表对应行修改，完全避免搜索词重复导致的误更新问题，同时解决原更新逻辑不生效的问题。

步骤1：声明窗体级变量存储选中行号 #

在用户窗体代码模块的最顶部（所有Sub过程之外）添加如下声明，让整个窗体的代码都可以读取选中的行号：

Private selectedRow As Long

步骤2：修改搜索代码，新增隐藏行号列 #

修改Search_Click代码，调整数组维度和列表框配置，新增一列存储原表行号并隐藏：

Private Sub Search_Click()
    ''''''''''''Validation
    If Trim(SearchTextBox.Value) = "" And Me.Visible Then
        MsgBox "Please enter a search value.", vbCritical, "Error"
        Exit Sub
    End If
    ' 数组增加1列存行号，维度改为0到18
    ReDim arrs(0 To 18, 1 To 1)
    M = 0 ' 避免重复搜索时计数累计错误
    With Worksheets("Sheet1")
        ListBox.Clear
        ListBox.ColumnCount = 19 ' 列数+1
        ListBox.ColumnHeads = True
        ListBox.Font.Size = 10
        ' 列宽最后加个0，隐藏最后1列的行号
        ListBox.ColumnWidths = "80,80,150,130,90,90,80,80,80,80,80,60,70,150,150,150,150,180,0"
        
        If .FilterMode Then .ShowAllData
        Set k = .Range("K2:K" & .Cells(.Rows.Count, "K").End(xlUp).Row).Find(What:="*" & SearchTextBox.Text & "*", LookIn:=xlValues, lookat:=xlWhole)
        If Not k Is Nothing Then
            adrs = k.Address
            Do
                M = M + 1
                ReDim Preserve arrs(0 To 18, 1 To M)
                For j = 0 To 17
                    arrs(j, M) = .Cells(k.Row, j + 1).Value
                Next j
                ' 新增：把原表行号存入最后一列
                arrs(18, M) = k.Row
                Set k = .Range("K2:K" & .Cells(.Rows.Count, "K").End(xlUp).Row).FindNext(k)
            Loop While Not k Is Nothing And k.Address <> adrs
            ListBox.Column = arrs
        Else
            ' If you get here, no matches were found
            MsgBox "No matches were found based on the search criteria.", vbInformation
        End If
    End With
End Sub

步骤3：修改双击事件，记录选中行号 #

修改ListBox_DblClick代码，双击时读取隐藏列的行号存入之前声明的变量：

Private Sub ListBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    ' 读取隐藏列的行号
    selectedRow = ListBox.Column(18)
    TextBox1.Text = ListBox.Column(0)
    TextBox2.Text = ListBox.Column(1)
    TextBox3.Text = ListBox.Column(2)
    TextBox4.Text = ListBox.Column(3)
    TextBox5.Text = ListBox.Column(4)
    TextBox6.Text = ListBox.Column(5)
    TextBox7.Text = ListBox.Column(6)
    TextBox8.Text = ListBox.Column(7)
    TextBox9.Text = ListBox.Column(8)
    TextBox10.Text = ListBox.Column(9)
    TextBox11.Text = ListBox.Column(10)
    TextBox12.Text = ListBox.Column(11)
    TextBox13.Text = ListBox.Column(12)
    TextBox14.Text = ListBox.Column(13)
    TextBox15.Text = ListBox.Column(14)
    TextBox16.Text = ListBox.Column(15)
    TextBox17.Text = ListBox.Column(16)
    TextBox18.Text = ListBox.Column(17)
End Sub

步骤4：替换原更新按钮代码 #

直接用存储的行号定位修改，不需要循环遍历所有行，既高效又不会误改其他记录：

Private Sub 更新按钮名_Click() ' 把“更新按钮名”改成你实际的更新按钮名称
    ' 先判断是否选中了记录
    If selectedRow = 0 Then
        MsgBox "请先双击选中要更新的记录", vbInformation
        Exit Sub
    End If
    With Sheets("Sheet1")
        .Cells(selectedRow, 1).Value = TextBox1.Text
        .Cells(selectedRow, 2).Value = TextBox2.Text
        .Cells(selectedRow, 3).Value = TextBox3.Text
        .Cells(selectedRow, 4).Value = TextBox4.Text
        .Cells(selectedRow, 5).Value = TextBox5.Text
        .Cells(selectedRow, 6).Value = TextBox6.Text
        .Cells(selectedRow, 7).Value = TextBox7.Text
        .Cells(selectedRow, 8).Value = TextBox8.Text
        .Cells(selectedRow, 9).Value = TextBox9.Text
        .Cells(selectedRow, 10).Value = TextBox10.Text
        .Cells(selectedRow, 11).Value = TextBox11.Text
        .Cells(selectedRow, 12).Value = TextBox12.Text
        .Cells(selectedRow, 13).Value = TextBox13.Text
        .Cells(selectedRow, 14).Value = TextBox14.Text
        .Cells(selectedRow, 15).Value = TextBox15.Text
        .Cells(selectedRow, 16).Value = TextBox16.Text
        .Cells(selectedRow, 17).Value = TextBox17.Text
        .Cells(selectedRow, 18).Value = TextBox18.Text
    End With
    MsgBox "记录更新成功", vbInformation
    ' 可选：更新完成后重新执行搜索，刷新列表框显示
    Call Search_Click
End Sub

原代码不生效的原因 #

你之前的更新逻辑是匹配K列值等于搜索框内容的所有行，存在两个问题：

1. 如果你修改了TextBox11（对应K列）的内容，匹配条件就会失效，对应的行不会被识别修改
2. 存在多个相同搜索词的记录时，所有匹配的行都会被覆盖修改

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