Right justify Listbox column
Results 1 to 7 of 7

Thread: Right justify Listbox column

  1. #1
    Board Regular
    Join Date
    Oct 2007
    Posts
    709
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Right justify Listbox column

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

    Thank you
    The trick with sneaky users is not teaching them anything lest they get wise!

  2. #2
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,513
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    1 Thread(s)

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,245
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Right justify Listbox column

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

    Example:
    Code:
    Private Sub CommandButton1_Click()
    'Add example
        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()
    'Load example
        ListBox1.List = Range("A2:D" & Range("A" & Rows.Count).End(xlUp).Row).Value
        ListBox1.Font.Name = "Courier New"
        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), "$ #,##0.00;-$ #,##0.00")
            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
    1 DATA DATA DATA VALUE
    2 asd1 efg1 zxc1 123.45
    3 asd2 efg2 zxc2 2000.743
    4 asd3 efg3 zxc3 34,587.21
    5 asd4 efg4 zxc4 $ 1,234,567.89
    Sheet1




    Last edited by DanteAmor; Jul 21st, 2019 at 01:31 PM.
    Regards Dante Amor

  4. #4
    Board Regular
    Join Date
    Oct 2007
    Posts
    709
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Right justify Listbox column

    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 by Av8tordude; Jul 21st, 2019 at 07:44 PM.
    The trick with sneaky users is not teaching them anything lest they get wise!

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,245
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Right justify Listbox column

    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.
    Regards Dante Amor

  6. #6
    Board Regular
    Join Date
    Oct 2007
    Posts
    709
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Right justify Listbox column

    Quote Originally Posted by DanteAmor View Post
    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 by Av8tordude; Jul 21st, 2019 at 08:47 PM.
    The trick with sneaky users is not teaching them anything lest they get wise!

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,245
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Right justify Listbox column

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

    Quote Originally Posted by Av8tordude View Post
    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

    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •