vba VbTab 对齐问题

VbTab alignment issue

提问by user1697952

I have a listbox in VBA Excel application, where it reads three different columns and lists their contents on a single line. But the width of each item in a column varies. Thus, when I display it in the list box using "vbtab" it is not aligning the next item properly. For example, when the first item has 4 characters, it pulls the second closer, whereas if the first item has 8 characters, it pushes the second item a little too far. Any idea how to fix this?

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

Below is the code I am using.

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

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

回答by Siddharth Rout

If your text width is known then you can use the .ColumnWidthsof the ListBox1to predefine the column width. This will ensure that the data is aligned properly. If the text width is not known then you can set the .ColumnWidthsto something which you feel will accommodate all words. In the below example I have set it to 50

如果您的文本宽度已知，那么您可以使用.ColumnWidths的ListBox1来预定义列宽。这将确保数据正确对齐。如果文本宽度未知，那么您可以将 设置为.ColumnWidths您认为可以容纳所有单词的内容。在下面的示例中，我已将其设置为50

The other trick is not to add the data in a loop to the ListBox1but to store it in an array and then set the .Listproperty of the ListBox1to that array. This will ensure that the execution of the code is faster.

另一个技巧不是将循环中的数据添加到 ，ListBox1而是将其存储在数组中，然后将 的.List属性设置ListBox1为该数组。这将确保代码的执行速度更快。

Here is an example. I am manually filling the array here. You can fill the array in your Do While Loop

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

CODE:

代码：

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

SCREENSHOT:

屏幕截图：

FOLLOWUP

跟进

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

抱歉，我不确定如何使我的编码适合数组并列出它们.. 你能帮我一下吗.. – user1697952 1 小时前

Try this (Untested)

试试这个（未经测试）

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

回答by Tim Williams

Siddharth's approach is better I think, but since you asked...

我认为悉达多的方法更好，但既然你问了......

Instead of this:

取而代之的是：

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

you can do this:

你可以这样做：

CPHlsttheeba.AddItem RPad(x & "   " & drovedate, 20) & _
                     RPad(drivenby, 20) & reason




'pad a string "s" on the right with spaces to total length "num"
Function RPad(s, num)
    RPad = Left(s & String(num," "), num)
End Function

You may need to adjust the amount of padding depending on how long your strings are. If you format your listbox using a fixed-with font then your "columns" should line up.

您可能需要根据字符串的长度调整填充量。如果您使用固定字体格式化列表框，那么您的“列”应该对齐。

回答by KurtisT

I had a similar situation creating a table in a MsgBox. I started with:

我在 MsgBox 中创建表时遇到了类似的情况。我开始于：

Item 1, tab, Item 2, tab, item 3.

项目 1，选项卡，项目 2，选项卡，项目 3。

But sometimes Item 1 or Item 2 was too long, requiring 2 tabs to keep things lined up. Some simple testing in the Immediate Window showed me that the default vbTab is 8 characters wide. So here is what I did inside a loop, then the MsgBox displayed this after the loop:

但有时 Item 1 或 Item 2 太长，需要 2 个选项卡才能保持排列。立即窗口中的一些简单测试显示默认的 vbTab 是 8 个字符宽。所以这是我在循环中所做的，然后 MsgBox 在循环之后显示了这个：

strMsg = strMsg & ary(1, m) & vbTab & IIf(Len(ary(1, m)) < 8, vbTab, "") & IIf(Len(ary(1, m)) < 16, vbTab, "") & ary(2, m) & vbTab & IIf(Len(ary(2, m)) < 8, vbTab, "") & ary(3, m) & vbCr

This essentially puts 2 or 3 tabs between results if the length of the previous result is small.

如果前一个结果的长度很小，这基本上会在结果之间放置 2 或 3 个选项卡。