Right justify Listbox column

[Av8tordude]

I have 4-column listbox. Is it possible to right Justify Only the 4th column?

Thank you

 

[footoo]

https://wellsr.com/vba/2017/excel/align-columns-differently-in-vba-userform-listbox/

 

[DanteAmor]

After loading or adding rows to your listbox make a call to the AlingColum4 procedure:

Example:

Private Sub CommandButton1_Click()
[COLOR=#008000]'Add example[/COLOR]
    ListBox1.AddItem "New"
    ListBox1.List(ListBox1.ListCount - 1, 1) = "col2"
    ListBox1.List(ListBox1.ListCount - 1, 2) = "col2"
    ListBox1.List(ListBox1.ListCount - 1, 3) = "12347.34"
    Call AlingColum4
End Sub


Private Sub UserForm_Activate()
[COLOR=#008000]'Load example[/COLOR]
    ListBox1.List = Range("A2:D" & Range("A" & Rows.Count).End(xlUp).Row).Value
    ListBox1.Font.Name = [COLOR=#ff0000]"Courier New"[/COLOR]
    ListBox1.ColumnWidths = "100;100;100;100"
    Call AlingColum4
End Sub


Sub AlingColum4()
    Dim i As Long, d As String, v As Long
    For i = 0 To ListBox1.ListCount - 1
        d = Format(ListBox1.List(i, 3), [COLOR=#ff0000]"$ #,##0.00;-$ #,##0.00"[/COLOR])
        v = Len(d)
        ListBox1.List(i, 3) = Space(20 - v) & d
    Next
End Sub

Note: It is important that the width of the character is correct to have a good alignment, for example "Courier New"
You must also set the number format you want.

-------------------

	A	B	C	D
DATA	DATA	DATA	VALUE
asd1	efg1	zxc1
asd2	efg2	zxc2
asd3	efg3	zxc3
asd4	efg4	zxc4

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]123.45[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2000.743[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"] 34,587.21 [/TD]

[TD="align: center"]5[/TD]

[TD="align: right"] $ 1,234,567.89 [/TD]

</tbody>

Sheet1​

 

[Av8tordude]

Excellent....This works great!!!!. Only one problem....How do I add my Headers?

Couple of things....

How do I make my dates "mm/dd/yy"
The last column is way off the listbox. My listbox size is limited to 375. I tried adjusting to the column width to ListBox1.ColumnWidths = "65;50;120;50" but still not helpful.

I see you use ListBox1.List instead of ListBox1.Rowsource. I tried to you ListBox1.Rowsource since it automatically adds the Headers, however it creates an error. Can you offer and explanation between .List and .Rowsource.

Thank you again

 

[DanteAmor]

With .List you can load an array of data.
With .RowSource loads a range of the sheet.

-----------
You can use .RowSource to load, but you can not add data with the .AddItem method.

-----------
You can not combine rowsorce and list, you want to use rowsource for titles, but you can not format each item.

-----------
One option is that you load with list as my example and the titles you put them with labels above the listbox.

 

[Av8tordude]

With .List you can load an array of data.
With .RowSource loads a range of the sheet.

-----------
You can use .RowSource to load, but you can not add data with the .AddItem method.

What if I'm not adding additional items to the list, for example, my code list all the data first then I load it to the listbox...see code below

Private Sub UserForm_Activate()Dim rData As Range
Dim LRow As Long, Cntr As Long


LRow = Range("W" & Rows.Count).End(xlUp).Row
Range("V17:Y" & LRow).ClearContents


Set rData = Range("A18", Range("B" & Rows.Count).End(xlUp))
rData.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("W18"), Unique:=True


With Range("W18").CurrentRegion
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlYes
    .Columns(3).Formula = "=SUMIF(" & rData.Columns(1).Address & ",W18," & rData.Columns(16).Address & ")"
End With
 
With Range("V17:Y17")
    .Cells(1, 1) = "Item"
    .Cells(1, 2) = "Ticker"
    .Cells(1, 3) = "Company"
    .Cells(1, 4) = "Total P/L"
End With
For Cntr = 18 To Range("W" & Rows.Count).End(xlUp).Row
    Range("V" & Cntr) = Cntr - 17
    Range("V" & Cntr).NumberFormat = "General"
Next Cntr


'Load example
ListBox1.List = Range("V18:Y" & Range("V" & Rows.Count).End(xlUp).Row).Value
ListBox1.Font.Name = "Courier New"
ListBox1.ColumnWidths = "50;50;165;50"
Call AlingColum4
End Sub

-----------
You can not combine rowsorce and list, you want to use rowsource for titles, but you can not format each item.

I'm guessing by your meaning that I will not be able to format the column? What I just want to add them but not format the titles

-----------
One option is that you load with list as my example and the titles you put them with labels above the listbox.

You say "One option", is there another options. This options seems cheesy to have lablels show on top of the listbox. I tried placing the lablel in front of the listbox but the label does not appear

 

[DanteAmor]

There are many questions, I will try to answer one by one.

What if I'm not adding additional items to the list, for example, my code list all the data first then I load it to the listbox...see code below

If you load with List you can format the column.
If you load with Rowsource you cannot format the column.

I'm guessing by your meaning that I will not be able to format the column? What I just want to add them but not format the titles

If you load with Rowsource you can add titles. This means that you cannot format the column.

You say "One option", is there another options. This options seems cheesy to have lablels show on top of the listbox. I tried placing the lablel in front of the listbox but the label does not appear

Those are the options, with titles and without titles, with format or without format.
You can search over the Spreadsheet or ListView controls

Spreadsheet Example

---------------
Listview Example