Solved

# listbox text align

Posted on 2006-05-23
2,742 Views
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
0
Question by:RyanBank

LVL 13

Accepted Solution

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()
End Sub
0

LVL 76

Assisted Solution

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

LVL 13

Expert Comment

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()
End Sub
0

LVL 17

Assisted Solution

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
'
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

Author Comment

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

Author Comment

found it
0

Author Comment

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

Thanks.
0

LVL 76

Expert Comment

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

## Featured Post

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…