ListBox Numbers Column Alignment

Shawn Michaels · Aug 19th 2016

Hi All,

Having searched through internet and almost all excel resources, I couldn't find any practical solution for numbers' column alignment in ListBox. A learner-developer friend has sent this code, which he found on excel forums (for convenience, I have attached the relevant workbook also)

Code

'Class Module
Option Explicit




Public Sub Center(LBox As MSForms.ListBox, Optional WhichColumn As Integer = 0)
'
' Center align listbox text
'
    Dim labSizer As MSForms.Label
    Dim lngIndex As Long
    Dim intColumn As Integer
    Dim lngTopIndex As Long
    
    ' get label control to help size text
    Set labSizer = m_GetSizer(LBox.Parent)
    If labSizer Is Nothing Then Exit Sub
    
    ReDim sngWidth(LBox.ColumnCount) As Single
    If Len(LBox.ColumnWidths) > 0 Then
        ' decode column widths
    Else
        ' assume default sizes
        For intColumn = 1 To LBox.ColumnCount
''            sngWidth(intColumn) = LBox.Width / LBox.ColumnCount
            sngWidth(intColumn) = (LBox.Width - (15 * LBox.ColumnCount)) / LBox.ColumnCount
        Next intColumn
        ''sngWidth(LBox.ColumnCount) = sngWidth(LBox.ColumnCount) - 15
    End If
    
    With labSizer
        With .Font
            .Name = LBox.Font.Name
            .Size = LBox.Font.Size
            .Bold = LBox.Font.Bold
        .Italic = LBox.Font.Italic
        End With
        .WordWrap = False
    End With
    
    lngTopIndex = LBox.TopIndex
    For intColumn = 1 To LBox.ColumnCount
        If intColumn = WhichColumn Or WhichColumn = -1 Then
            For lngIndex = 0 To LBox.ListCount - 1
                LBox.TopIndex = lngIndex
                labSizer.Width = LBox.Width
                labSizer.Caption = Trim(LBox.List(lngIndex, intColumn - 1))
                labSizer.AutoSize = True
                Do While labSizer.Width < sngWidth(intColumn)
                    labSizer.Caption = " " & labSizer.Caption & " "
                Loop
                LBox.List(lngIndex, intColumn - 1) = labSizer.Caption
            Next lngIndex
        End If
    Next intColumn
    LBox.TopIndex = lngTopIndex
    
    LBox.Parent.Controls.Remove labSizer.Name
    Set labSizer = Nothing




End Sub
Public Sub Left(LBox As MSForms.ListBox, Optional WhichColumn As Integer = 0)
'
' Left align listbox text.
' This is the default so just add text in
'
    Dim lngIndex As Long
    Dim intColumn As Integer
    Dim lngTopIndex As Long
    ReDim sngWidth(LBox.ColumnCount) As Single
    
    If Len(LBox.ColumnWidths) > 0 Then
        ' decode column widths
    Else
        ' assume default sizes
        For intColumn = 1 To LBox.ColumnCount
            sngWidth(intColumn) = (LBox.Width - (15 * LBox.ColumnCount)) / LBox.ColumnCount
        Next intColumn
    End If
    
    lngTopIndex = LBox.TopIndex
    For intColumn = 1 To LBox.ColumnCount
        If intColumn = WhichColumn Or WhichColumn = -1 Then
            For lngIndex = 0 To LBox.ListCount - 1
                LBox.TopIndex = lngIndex
                LBox.List(lngIndex, intColumn - 1) = Trim(LBox.List(lngIndex, intColumn - 1))
            Next lngIndex
        End If
    Next intColumn
    LBox.TopIndex = lngTopIndex
    
End Sub




Public Sub Right(LBox As MSForms.ListBox, Optional WhichColumn As Integer = 1)
'
' Right align listbox text
'
    Dim labSizer As MSForms.Label
    Dim lngIndex As Long
    Dim intColumn As Integer
    Dim lngTopIndex As Long
    
    ' get label control to help size text
    Set labSizer = m_GetSizer(LBox.Parent)
    If labSizer Is Nothing Then Exit Sub
    
    ReDim sngWidth(LBox.ColumnCount) As Single
    If Len(LBox.ColumnWidths) > 0 Then
        ' decode column widths
    Else
        ' assume default sizes
        For intColumn = 1 To LBox.ColumnCount
            sngWidth(intColumn) = (LBox.Width - (15 * LBox.ColumnCount)) / LBox.ColumnCount
        Next intColumn
    End If
    
    With labSizer
        With .Font
            .Name = LBox.Font.Name
            .Size = LBox.Font.Size
            .Bold = LBox.Font.Bold
        .Italic = LBox.Font.Italic
        End With
        .WordWrap = False
    End With
    
    lngTopIndex = LBox.TopIndex
    For intColumn = 1 To LBox.ColumnCount
        If intColumn = WhichColumn Or WhichColumn = -1 Then
            For lngIndex = 0 To LBox.ListCount - 1
                LBox.TopIndex = lngIndex
                labSizer.Width = LBox.Width
                labSizer.Caption = Trim(LBox.List(lngIndex, intColumn - 1))
                labSizer.AutoSize = True
                Do While labSizer.Width < sngWidth(intColumn)
                    labSizer.Caption = " " & labSizer.Caption
                Loop
                LBox.List(lngIndex, intColumn - 1) = labSizer.Caption
            Next lngIndex
        End If
    Next intColumn
    LBox.TopIndex = lngTopIndex
    LBox.Parent.Controls.Remove labSizer.Name
    Set labSizer = Nothing




End Sub
Private Property Get m_GetSizer(Base As MSForms.UserForm) As MSForms.Label
    Set m_GetSizer = Base.Controls.Add("Forms.Label.1", "labSizer", True)
End Property

Display More

Userform Code:
Code:

Code

Option ExplicitPrivate m_clsLBoxAlign As CListboxAlignPrivate Sub OptionButton1_Click()    m_clsLBoxAlign.Left Me.ListBox1, ListBox2.ListIndex - 1End SubPrivate Sub OptionButton2_Click()    m_clsLBoxAlign.Center ListBox1, ListBox2.ListIndex - 1End SubPrivate Sub OptionButton3_Click()    m_clsLBoxAlign.Right ListBox1, ListBox2.ListIndex - 1End SubPrivate Sub UserForm_Initialize()    Dim lngRow As Long    Dim lngIndex As Long        ListBox1.ColumnCount = 2    With Range("Sheet1!A1")        Do While .Offset(lngRow, 0) <> ""            ListBox1.AddItem .Offset(lngRow, 0).Text            For lngIndex = 1 To ListBox1.ColumnCount                ListBox1.List(lngRow, lngIndex) = .Offset(lngRow, 0).Text            Next            lngRow = lngRow + 1        Loop    End With        Set m_clsLBoxAlign = New CListboxAlign    With ListBox2        .AddItem "All Columns"        .AddItem "----Select Column---"        .AddItem "Column 1"        .AddItem "Column 2"    End With    End SubPrivate Sub UserForm_Terminate()    Set m_clsLBoxAlign = NothingEnd Sub

Now, I do know what class modules are but I don't know the coding structure at all.

There is a listbox (listBox1) on the userform (Userform1), which shows Sheet 1 Column A data in 2 columns on ListBox (same data in 2 columns). We can select the column from ListBox 2 radio button, and then select radio buttons in Frame one, the that particular column on listbox (Column 1 or Column 2) will align according to the selected option.

This seems to be a very advanced question but nevertheless I am taking my chance.

Can an expert kindly help in how to change the row source for ListBox1 and include multiple columns and change the alignment of multiple columns individually with radio buttons ?

Your help will be highly appreciated.

Thank you for your kind support.

**********************************************************************************
NOTE: This question has been posted on another forum too, here's the link:
http://www.mrexcel.com/forum/e…ers-column-alignment.html
**********************************************************************************

**cytop** · Aug 20th 2016

Re: ListBox Numbers Column Alignment

There is no RowSource - the items are loaded in code in the userform Init event.

This would have been obvious if you had done some basic debugging of the code - boring as it may seem, this is an integral part of developing any application in any language, and is something that will save your time if you learn how...

Shawn Michaels · Aug 20th 2016

Re: ListBox Numbers Column Alignment

Hi Cytop,

Thank you for your reply. Yes, the data is loaded during initialization of the UF. This is my question exactly:

(1) how we can have dynamic row source (for example, A1 to E10), with multiple some text columns and some numbers columns

Example: I have tried this:

Code

Me.ListBox1.RowSource = Range(Sheet1.Range("A2"), Sheet1.Range("E65536").End(xlUp)).Address(, , , True)

But the UF shows only 3 columns, then when press any radio button for alignment, it gives Permission Denied error.

(2) how we can change the alignment of numbers column (TextAlignRight) and text (TextAlignLeft)

The code in OP is showing Column A twice in ListBox and the radio button gives option for only 2 columnns alignment in ListBow.

Thank you for your support.

Profound Regards,
Shawn

**cytop** · Aug 20th 2016

Re: ListBox Numbers Column Alignment

Your question was

Quote

help in how to change the row source for ListBox1

I was just correcting slightly as Row Source (or more correctly 'RowSource') is a property of a ListBox and just wanted to make sure there was no misunderstanding.

Quote

and include multiple columns

I don't understand why you ask this question - the original code already includes multiple columns.

So to load from the range A1 to E10 you simply change the code that loads the listbox to load from that range rather than looping until a blank cell in found in Col A

Setting a column right aligned is simply a matter of calling the class method passing the column index to change.

Quote

showing Column A twice

The issue here is in this line - An Offset of 0 from Col A gives you ... the value in Col A...

Code

ListBox1.List(lngRow, lngIndex) = .Offset(lngRow, 0).Text

Really would suggest you read the page on debugging - it would be a time saver for you.

Add

Code

ListBox1.ColumnCount = 2 


    '// New line
    Debug Assert False


    With Range("Sheet1!A1")

And use F8 to step the code line by line querying the values of the various variables (see the linked page) for details.

Shawn Michaels · Aug 20th 2016

Re: ListBox Numbers Column Alignment

I tried Debug.Assert False but nothing happens, the loop continues.

I tried hard-coding the RowSource. The UF shows the required number of columns but clicking on the radio buttons gives Permission Denied.

I guess I'll have to hire help

Anyways, thank you for your help.

May God Bless You and Your Family and Give You the Desire of Your Heart and Fulfil all Your Dreams :thumbcoo:

**mikerickson** · Aug 20th 2016

Re: ListBox Numbers Column Alignment

Do not use the .RowSource of the list box, that code demands that you fill the listbox other ways.

Fortunatly, the replacement is simple

Rather than

Code

Me.ListBox1.RowSource = Range(Sheet1.Range("A2"), Sheet1.Range("E65536").End(xlUp)).Address(, , , True)

use

Code

Me.ListBox1.List = Range(Sheet1.Range("A2"), Sheet1.Range("E65536").End(xlUp)).Value

I consider RowSource to be one of those "sounded like a good idea at the time" ideas that Microsoft occasionally has, somewhat like merged cells.

Shawn Michaels · Aug 20th 2016

Re: ListBox Numbers Column Alignment

Hi Mike,

You are the BEST!!!!

Thank you so much for your help. It works amazing.

Profoundest Regards

Shawn Michaels · Aug 20th 2016

Re: ListBox Numbers Column Alignment

One more question Mike, Is there anyway we can make the column alignment default. With the above codes, the user has to manually choose each column's desired alignment each time the UserForm opens and closes?

Thank you

**mikerickson** · Aug 20th 2016

Re: ListBox Numbers Column Alignment

Column Alignment is not a feature of Excel's listbox.
It is a constructed feature of the custom Object described in the class module.

Excel cannot be set to default to a custom object. (i.e. the ToolBox cannot be set to insert this custom object rather than Excel's built in ListBox object)

Shawn Michaels · Aug 23rd 2016

Re: ListBox Numbers Column Alignment

Hi Mike,

I do understand that issue now.

However, given the above Class Module, I am not able to change my ListBox Column Width, if I do that, everything column is kind of squished up to left. What needs to be changed in the code above in order to have required column width?

Thank you for your help.

Regards

**mikerickson** · Aug 23rd 2016

Re: ListBox Numbers Column Alignment

Set the .ColumnWidths property before clicking any of the option buttons.

Shawn Michaels · Aug 23rd 2016

Re: ListBox Numbers Column Alignment

I have tried that just now, it does set the required columns' width but then I f I select column (numbers' column) and click the options button, the numbers column alignment does not work.

**mikerickson** · Aug 26th 2016

Re: ListBox Numbers Column Alignment

I'm sorry it took me so long to reply.
I had a lot of trouble making your "pad with spaces" approach work, that I devised my own solution.
I'm finishing up the demo and I'll post it by tomorrow at the latest.
Its a custom AlignListbox object that has many of the same properties as a ListBox. I hope its straight forward for you to use.
Leter.

BTW, I'm sure that you frequently use a ListBox's Change and Click events, are there any other ListBox events that you use frequently. (don't say Enter, Exit, BeforeUpdate or AfterUpdate )

ONE MORE QUESTION: My Mac doesn't support RowSource, but it sounds like you are familiar with it. From what I can gather, RowSource is a string, the address of cells where the Listbox gets its values. One thing I'm not clear on is whether it establishes a two way connection. If the RowSource = "A1:A10" and one changes the ListBox1.List(2) with code, is that change reflected in the worksheet as well as in the list box?

Shawn Michaels · Aug 27th 2016

Re: ListBox Numbers Column Alignment

Hi Mike,

Thank you for your reply. You are one of the greatest developers (Number 1 in my Top 5 )

Basically, I have a UF (userform) and 2 controls: LB (listbox) and TB (textbox). My basic coding and developing strategy is to use RowSource property for LB when the UF initializes (the original RowSource)

Then there is a TB, which I use to search. When I type in TB, Advanced filter macro runs and then I use those results as RowSource for the same LB. So effectively I reset the original RowSource and use the results of Advanced Filter as new source to display in the same LB. So there will be no change required in the worksheet.

I am not sure about List property and its use given the straight forward nature of data display. The other thing is to have number's formatting (with commas and 2 zeros after the decimal.

Thank you for your kind support.

**mikerickson** · Aug 27th 2016

Re: ListBox Numbers Column Alignment

Thats very different that the impression I got from the OP.

The OP lead me to believe that your issue was controling the alignment of the strings in a Listbox's columns. (And I've had a good time developing a custom control that does that.)

Now it sound like there is also the (additional) desire for a TextBox that accepts number formatting.

I'll get back to you on both of them.

Shawn Michaels · Aug 28th 2016

Re: ListBox Numbers Column Alignment

Hi Mike,

Its fine. The TextBox is for another purpose. The main issue remains the text alignment of each column in ListBox.

Thank you

**mikerickson** · Aug 28th 2016

Re: ListBox Numbers Column Alignment

In the attached, there is code for clsAlignListBox. As well as two demonstration user forms.

To utilize this object, the developer should make a userform and put a normal ListBox in the appropriate location and set the defaults for that ListBox (.Font, .BackColor, .ColumnCount, .ColumnWidth, etc.) as they desire.

Then, in the userform's code module, they should declare a public variable, with events as clsAlignListBox. Set the AlignListBox.ListBox property to the ListBox. Thereafter, they ignore the ListBox and deal only with the AlignListBox.
The properties and methods used for clsAlignListbox are almost identical for using a MSForms.Listbox

Code

' in userform code module


Public WithEvents AlignListBox1 as clsAlignListBox


Private Sub Userform_Initialize()
    Set AlignListBox1 = New clsAlignListBox
    Set AlignListBox1 = Me.ListBox1
    ' ...
End Sub


Private Sub Userform_Terminate()
    AlignListBox1.Delete
End Sub

Display More

I hope this helps you

Shawn Michaels · Aug 28th 2016

Re: ListBox Numbers Column Alignment

Great Mike ! This works like a charm. Thank you very much for your efforts for Developers Community and all other excel users. This was missing from the scene and now we can use the ListBox with column alignment and formatting.

Please give me some time, I'll thoroughly test it for various data sets and come back to you.

Many many thank

**mikerickson** · Aug 28th 2016

Re: ListBox Numbers Column Alignment

Let me know. Once I get the column widths issues resolve and get this thing robust, this technique is lots of possibilities. (differential column formatting, visible grid lines, variable row heights, long list items that wrap rather than disappear off the side)

Shawn Michaels · Aug 29th 2016

Re: ListBox Numbers Column Alignment

Hi Mike,

All the rest is fine except for Demo userform. It shows an error on position sizer procedure.

Thank you

ListBox Numbers Column Alignment

Participate now!

Share