excel - VbTab 对齐问题

Question

我在 VBA Excel 应用程序中有一个列表框，它读取三个不同的列并在一行中列出它们的内容。但是列中每个项目的宽度各不相同。因此，当我使用“vbtab”在列表框中显示它时，它没有正确对齐下一个项目。例如，当第一项有 4 个字符时，它将第二项拉得更近，而如果第一项有 8 个字符，则将第二项推得太远。知道如何解决这个问题吗？

下面是我正在使用的代码。

Private Sub UserForm_Activate()
With ThisWorkbook.Sheets("Sheet1").Range("a1:a50")
MySearch = Array("Tba")
For i = LBound(MySearch) To UBound(MySearch)
Set rng = .Find(what:=MySearch(i), _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

firstaddress = rng.Address


Do
j = 1
drovedate = rng.Offset(0, j)
j= j + 1
drivenby = rng.Offset(0, j)
j = j + 6
reason = rng.Offset(0, j)
x = x + 1
Dim LineOfText As String

CPHlsttheeba.AddItem (x & "   " & drovedate & vbTab() & vbTab & drivenby & vbTab & vbTab & reason)


Set rng = .FindNext(rng)
Loop While Not rng Is Nothing And rng.Address <> firstaddress
Next i
End With
End Sub

Answer 1

如果您的文本宽度已知，那么您可以使用 .ColumnWidths的ListBox1预定义列宽。这将确保数据正确对齐。如果文本宽度未知，则可以设置 .ColumnWidths你觉得可以容纳所有词语的东西。在下面的示例中，我将其设置为 50
另一个技巧是不要将循环中的数据添加到 ListBox1但要将其存储在数组中，然后设置 .List ListBox1 的属性(property)到那个数组。这将确保代码的执行速度更快。

这是一个例子。我在这里手动填充数组。您可以在 Do While Loop 中填写数组

代码 :

Private Sub UserForm_Activate()
    Dim Myarray(1 To 2, 1 To 4) As String

    Myarray(1, 1) = "Sid"
    Myarray(1, 2) = "Apple"
    Myarray(1, 3) = "Banana"
    Myarray(1, 4) = "Mumbai"
    Myarray(2, 1) = "New Delhi"
    Myarray(2, 2) = "New York"
    Myarray(2, 3) = "Japan"
    Myarray(2, 4) = "asdfghjkl"

    With Me.ListBox1
        .Clear
        .ColumnHeads = False
        .ColumnCount = 4

        .List = Myarray

        '~~> Change 50 to 8 in your application
        .ColumnWidths = "50;50;50;50"
        .TopIndex = 0
    End With
End Sub

屏幕截图 :



跟进

Sorry, i am not sure how to fit my codings to work with an array and to list them.. Would you be able to help me out a bit.. – user1697952 1 hour ago

试试这个(未经测试)

Private Sub UserForm_Activate()
    Dim n As Long

    With CPHlsttheeba
        .ColumnHeads = False
        .ColumnCount = 4
        .ColumnWidths = "8;8;8;8"
    End With

    With ThisWorkbook.Sheets("Sheet1").Range("a1:a50")
        MySearch = Array("Tba")
        For i = LBound(MySearch) To UBound(MySearch)
            Set rng = .Find(what:=MySearch(i), _
            After:=.Cells(.Cells.Count), _
            LookIn:=xlFormulas, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False)

            firstaddress = rng.Address

            Do
                j = 1
                drovedate = rng.Offset(0, j)
                j = j + 1
                drivenby = rng.Offset(0, j)
                j = j + 6
                reason = rng.Offset(0, j)
                x = x + 1
                Dim LineOfText As String

                CPHlsttheeba.AddItem "Test" & n, n
                CPHlsttheeba .List(n, 0) = drovedate
                CPHlsttheeba .List(n, 1) = drivenby
                CPHlsttheeba .List(n, 2) = reason

                n = n + 1

                Set rng = .FindNext(rng)
            Loop While Not rng Is Nothing And _
            rng.Address <> firstaddress
        Next i
    End With
End Sub