listbox text align

Hi,

Please assist how can we align the values to space evenly in a listbox?

presently displayed as:
1,12,500,1,20
5,4,20,4,3

desired output:
  1  ,  12  ,  500  ,  1  ,  20     aligning with spaces and placing 3 spaces before and after the comma.
  5  ,   4   ,    20  ,  4  ,   3

thanks

    For I = 0 To 95 Step 2
        List1.AddItem Arr_Gen(I) & "," & _
                Arr_Gen(I + 96) & "," & _
                Arr_Gen(I + 192) & "," & _
                Arr_Gen(I + 288) & "," & _
                Arr_Gen(I + 384) & "," & _
                Arr_Gen(I + 480) & "," & _
                Arr_Gen(I + 576)
    Next
RyanBankAsked:
Who is Participating?
 
iHadiConnect With a Mentor Commented:
Hi  RyanBank

Add a listbox and a command button on your form and paste the following code:

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 & ","
Next
FormatNumbers = OutStr
End Function

Private Sub Command1_Click()
List1.AddItem FormatNumbers("1,12,500,1,20")
List1.AddItem FormatNumbers("5,4,20,4,3")
End Sub
0
 
GrahamSkanConnect With a Mentor RetiredCommented:
You will need to use a fixed-width font, and also to vary the number of spaces according to the number of digits in each piece of data.

    List1.Font.Name = "Courier New"
    For i = 0 To 95 Step 2
        List1.AddItem Arr_Gen(i) & "   ," & _
                Right$("   " & (Arr_Gen(i + 96)), 3) & "   ," & _
                Right$("   " & (Arr_Gen(i + 192)), 3) & "   ," & _
                Right$("   " & (Arr_Gen(i + 288)), 3) & "   ," & _
                Right$("   " & (Arr_Gen(i + 384)), 3) & "   ," & _
                Right$("   " & (Arr_Gen(i + 480)), 3) & "   ," & _
                Right$("   " & (Arr_Gen(i + 576)), 3)
    Next
0
 
iHadiCommented:
Or try this:

Hi  RyanBank

Add a listbox and a command button on your form and paste the following code:

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 & "   " & Format(tmpAr(i),"00#") & tmpAr(i) & "   "
    If i <> UBound(tmpAr) Then OutStr = OutStr & ","
Next
FormatNumbers = OutStr
End Function

Private Sub Command1_Click()
List1.AddItem FormatNumbers("1,12,500,1,20")
List1.AddItem FormatNumbers("5,4,20,4,3")
End Sub
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
zzzzzoocConnect With a Mentor Commented:
You can try setting tab-stops.

Form1:
-----------------
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()
    '
End Sub
Private Sub Command1_Click()
    Dim tabs(4) As Long
    tabs(0) = 30
    tabs(1) = 60
    tabs(2) = 90
    tabs(3) = 120
    tabs(4) = 150
    Call SendMessage(List1.hWnd, LB_SETTABSTOPS, 5, tabs(0))
    Call List1.AddItem("1" & vbTab & _
                       "22" & vbTab & _
                       "333" & vbTab & _
                       "4444" & vbTab & _
                       "55555")
    Call List1.AddItem("333" & vbTab & _
                       "55555" & vbTab & _
                       "1" & vbTab & _
                       "4444" & vbTab & _
                       "22")
    Call List1.AddItem("22" & vbTab & _
                       "4444" & vbTab & _
                       "333" & vbTab & _
                       "55555" & vbTab & _
                       "1")
End Sub
0
 
RyanBankAuthor Commented:
iHadi,

when I entered  List1.AddItem FormatNumbers("11:00 PM,12,500,1,20")

returned invalid call or procedure and pointing to:

    OutStr = OutStr & "   " & String((3 - Len(tmpAr(I))) * 2, " ") & tmpAr(I) & "   "

thanks.
0
 
RyanBankAuthor Commented:
found it
0
 
RyanBankAuthor Commented:
iHadi,

Is it possible to align properly when we have negative values? The sets ive tried, how can we troubleshoot to align with negative values?

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")

Thanks.
0
 
GrahamSkanRetiredCommented:
It's not clear whch solution you are using, but I can only see a problem that if the value is -100 or less (needs more than 3 places) .
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.