Right justify Listbox column

Av8tordude

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

Thank you
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,216
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
859
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,216
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
859
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,216
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,102,758
Messages
5,488,675
Members
407,651
Latest member
Halosty

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top