Excel VBA中用UserForm更新工作表行数据仅单个单元格生效问题求助

我尝试通过VBA UserForm的ListBox更新工作表某一行的多个单元格值，但只有单个单元格更新成功，其余目标单元格未更新。以下是相关代码，恳请帮忙排查原因：

按钮点击事件代码 #

Private Sub Update_Click()
    ' textbox11存储行的ID（数字）
    If Me.TextBox11.Value = "" Then
        MsgBox "Error"
        Exit Sub
    End If
    
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    Dim selected_row As Long
    selected_row = Application.WorksheetFunction.Match(CLng(Me.TextBox11.Value), sh.Range("A:A"), 0)
    
    sh.Range("B" & selected_row).Value = Me.ComboBox1.Value
    sh.Range("C" & selected_row).Value = Me.TextBox1.Value
    sh.Range("D" & selected_row).Value = Me.TextBox2.Value
    sh.Range("O" & selected_row).Value = Me.TextBox3.Value
    sh.Range("S" & selected_row).Value = Me.TextBox4.Value
    sh.Range("Y" & selected_row).Value = Me.TextBox6.Value
    sh.Range("U" & selected_row).Value = Me.TextBox7.Value
    sh.Range("E" & selected_row).Value = Me.ComboBox2.Value
    sh.Range("T" & selected_row).Value = Me.ComboBox3.Value
    sh.Range("V" & selected_row).Value = Me.ComboBox4.Value
    sh.Range("Z" & selected_row).Value = Me.ComboBox5.Value
    sh.Range("AA" & selected_row).Value = Me.ComboBox6.Value
    
    ' 更新后清空用户窗体控件内容
    Me.ComboBox1.Value = ""
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
    Me.TextBox11.Value = ""
    Me.TextBox12.Value = ""
    Me.ComboBox2.Value = ""
    Me.TextBox3.Value = ""
    Me.TextBox4.Value = ""
    Me.ComboBox3.Value = ""
    Me.TextBox6.Value = ""
    Me.TextBox7.Value = ""
    Me.ComboBox4.Value = ""
    Me.ComboBox5.Value = ""
    Me.ComboBox6.Value = ""

    Call Refresh_Data
End Sub

ListBox点击事件代码 #

Private Sub ListBox1_Click()
    Me.TextBox11.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
    Me.ComboBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
    Me.TextBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 2)
    Me.TextBox2.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 3)
    Me.ComboBox2.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 4)
    Me.TextBox3.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 13)
    Me.TextBox6.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 24)
    Me.TextBox4.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 18)
    Me.ComboBox3.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 19)
    Me.TextBox7.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 20)
    Me.ComboBox4.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 21)
    Me.ComboBox5.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 25)
    Me.ComboBox6.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 26)
    Me.TextBox12.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
End Sub

排查方向及修正方案 #

1. ListBox列索引不匹配（核心问题）
   工作表列与ListBox列索引对应逻辑错误：工作表A列对应ListBox的第0列，那么O列（工作表第15列）应对应ListBox的第14列，但代码中用了第13列，导致TextBox3取到错误的值，更新时自然无法写入正确内容。
   修正后的ListBox点击事件代码：

   Private Sub ListBox1_Click()
       Me.TextBox11.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
       Me.ComboBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
       Me.TextBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 2)
       Me.TextBox2.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 3)
       Me.ComboBox2.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 4)
       Me.TextBox3.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 14) ' 修正为14，对应工作表O列
       Me.TextBox6.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 24)
       Me.TextBox4.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 18)
       Me.ComboBox3.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 19)
       Me.TextBox7.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 20)
       Me.ComboBox4.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 21)
       Me.ComboBox5.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 25)
       Me.ComboBox6.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 26)
       Me.TextBox12.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
   End Sub
   

2. 添加Match函数错误处理
   原代码中WorksheetFunction.Match若找不到匹配项会直接报错中断程序，建议改用Application.Match并添加错误判断：

   Private Sub Update_Click()
       If Me.TextBox11.Value = "" Then
           MsgBox "错误：未选择行"
           Exit Sub
       End If
       
       Dim sh As Worksheet
       Set sh = ThisWorkbook.Sheets("Sheet1")
       Dim selected_row As Variant
       
       selected_row = Application.Match(CLng(Me.TextBox11.Value), sh.Range("A:A"), 0)
       ' 判断是否找到匹配行
       If IsError(selected_row) Then
           MsgBox "未找到对应ID的行"
           Exit Sub
       End If
       
       ' 后续赋值代码保持不变...
       
       Call Refresh_Data
   End Sub
   

3. 验证控件赋值正确性
   点击ListBox后，检查所有TextBox/ComboBox是否正确显示对应数据。可在Update_Click开头添加调试代码，查看控件值是否正常：

   Debug.Print "ComboBox1: " & Me.ComboBox1.Value, "TextBox1: " & Me.TextBox1.Value, "TextBox3: " & Me.TextBox3.Value
   

   打开VBA编辑器的「即时窗口」查看输出，确认控件值是否为空或错误。

4. 检查Refresh_Data过程影响
   确认Refresh_Data过程是否会重新加载ListBox或修改工作表数据，导致刚更新的值被覆盖。

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