Excel中鼠标调整ListBox列宽时出现自动化错误的求助

你的方案完全可行，问题出在修改列宽时ListBox的滚动位置异常重置，以及控件对象与客户端的临时连接中断，以下是具体原因分析和修复方案：

问题根源 #

当ListBox滚动到底部时修改ColumnWidths属性，会触发控件内部的重绘逻辑，导致列表自动滚动回顶部；同时重绘过程中控件对象和客户端的连接临时断开，引发自动化错误。后续调试时出现的400错误，是因为窗体已处于显示状态，重复调用Show方法导致冲突。

修复后的完整代码 #

ThisWorkbook代码（无需修改） #

Private Sub Workbook_Open()
    UserForm1.Show
End Sub

UserForm1修复代码 #

Public drag As Boolean
Private scrollPos As Long ' 用于保存调整列宽前的滚动位置

Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Dim wdth As Single
    Dim str As String
    
    str = Me.ListBox1.ColumnWidths
    str = Replace(str, ".", ",")
    wdth = CSng(Left(str, InStr(str, " pt")))
    drag = False
    If Y < 10 Then
        If Abs(wdth - X) < 5 Then
            scrollPos = Me.ListBox1.TopIndex ' 记录当前滚动位置
            drag = True
        End If
    End If
End Sub

Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    drag = False
End Sub

Private Sub ListBox1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Dim wdth1n, wdth2n As Single
    
    If drag Then
        wdth1n = X
        wdth2n = Me.ListBox1.Width - X - 10
        
        If wdth1n > 20 And wdth2n > 20 Then
            ' 禁用控件避免重绘时的滚动跳转和对象连接异常
            Me.ListBox1.Enabled = False
            Me.ListBox1.ColumnWidths = Replace(wdth1n & " pt;" & wdth2n & " pt", ",", ".")
            Me.ListBox1.TopIndex = scrollPos ' 恢复之前的滚动位置
            Me.ListBox1.Enabled = True
        End If
    End If
End Sub

Private Sub UserForm_Initialize()
    Dim i As Integer
    
    drag = False
    Me.ListBox1.ColumnWidths = Me.ListBox1.Width / 2 & " pt;" & Me.ListBox1.Width / 2 & " pt"
    For i = 1 To 40
        Me.ListBox1.AddItem ThisWorkbook.Sheets(1).Range("A" & i).Value
        Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = ThisWorkbook.Sheets(1).Range("A" & i).Value
    Next i
End Sub

核心修复要点 #

• 新增scrollPos变量，在开始拖动列分隔线时记录当前滚动位置，修改列宽后恢复该位置
• 修改ColumnWidths前先禁用ListBox，避免重绘过程中出现滚动跳转和控件对象连接中断
• 列宽调整完成后重新启用控件，确保交互正常

调整后，无论列表处于顶部还是底部，拖动列分隔线调整宽度时都不会出现滚动跳转和自动化错误，列宽调整功能可以稳定运行。

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