aligning listbox data

Posted on 2006-05-24
Last Modified: 2010-04-07

Please assist how can we align properly when negative values are present.


Private Function FormatNumbers(iNumbers As String) As String
Dim tmpAr() As String
Dim OutStr As String
tmpAr = Split(iNumbers, ",")

For i = LBound(tmpAr) To UBound(tmpAr)
    OutStr = OutStr & "   " & String((3 - Len(tmpAr(i))) * 2, " ") & tmpAr(i) & "   "
    If i <> UBound(tmpAr) Then OutStr = OutStr & ","
FormatNumbers = OutStr
End Function

Private Sub Command1_Click()
List1.AddItem FormatNumbers("1,12,50,1,20")
List1.AddItem FormatNumbers("1,12,0,1,200")
List1.AddItem FormatNumbers("50,-4,2,-4,-3")
List1.AddItem FormatNumbers("-5,-4,-2,50,-3")
List1.AddItem FormatNumbers("-5,-4,-2,-4,3")
List1.AddItem FormatNumbers("1,1,-12,1,20")
List1.AddItem FormatNumbers("12,12,500,111,20")
List1.AddItem FormatNumbers("1,12,5,10,20")
End Sub
Question by:RyanBank
    LVL 3

    Expert Comment

    try this in your function

    For i = LBound(tmpAr) To UBound(tmpAr)
        OutStr = OutStr & Right(Space(10) & tmpAr(i), 10)
        If i <> UBound(tmpAr) Then OutStr = OutStr & ","
    LVL 17

    Accepted Solution

    Have you tried my suggestion from the previous question? This is a simplified version.

    Option Explicit

    Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long

    Private Const LB_SETTABSTOPS = &H192
    Private Sub Form_Load()
        'change the values below to change the tabstop positions
        Call ListInit(List1, 30, 60, 90, 120, 150)
    End Sub
    Private Sub Command1_Click()
        Call ListAdd(List1, 1, 12, 50, 1, 20)
        Call ListAdd(List1, -5, -4, -2, 50, -3)
        Call ListAdd(List1, 12, 12, 500, 111, 20)
    End Sub
    Private Sub ListInit(ByRef lst As ListBox, ParamArray tabs() As Variant)
        Call SendMessage(lst.hWnd, LB_SETTABSTOPS, (UBound(tabs) - 1), tabs(0))
    End Sub
    Private Sub ListAdd(ByRef lst As ListBox, ParamArray vals() As Variant)
        Dim temp As String
        Dim i As Integer
        For i = LBound(vals) To UBound(vals)
            If (i = LBound(vals)) Then
                temp = vals(i)
                temp = temp & vbTab & vals(i)
            End If
        Call lst.AddItem(temp)
    End Sub

    Author Comment


    I found an interesting bug that sadly I'm not sure how to fix, the code works perfectly when run, but when you compile it and run the exe it will tab so far for the first element. Please assist what could have happend


    1                         2 3 4 5
    4                         4 5 6 7

    LVL 17

    Expert Comment

    I can't reproduce that behavior.

    You can try adjusting the tabstop positions at:
    Call ListInit(List1, 30, 60, 90, 120, 150)


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now