Right justify Listbox column

Av8tordude

Well-known Member
Joined
Oct 13, 2007
Messages
854
I have 4-column listbox. Is it possible to right Justify Only the 4th column?

Thank you
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,095
Office Version
2007
Platform
Windows
After loading or adding rows to your listbox make a call to the AlingColum4 procedure:

Example:
Code:
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.

-------------------
ABCD
1DATADATADATAVALUE
2asd1efg1zxc1123.45
3asd2efg2zxc22000.743
4asd3efg3zxc3 34,587.21
5asd4efg4zxc4 $ 1,234,567.89

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1




 
Last edited:

Av8tordude

Well-known Member
Joined
Oct 13, 2007
Messages
854
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
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,095
Office Version
2007
Platform
Windows
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

Well-known Member
Joined
Oct 13, 2007
Messages
854
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

Code:
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
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,095
Office Version
2007
Platform
Windows
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

 

Watch MrExcel Video

Forum statistics

Threads
1,101,851
Messages
5,483,322
Members
407,393
Latest member
GeorgeBrown

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top