ListBox Numbers Column Alignment

[Michaels]

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:

'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

Userform Code:

Option Explicit


Private m_clsLBoxAlign As CListboxAlign


Private Sub OptionButton1_Click()
    m_clsLBoxAlign.Left Me.ListBox1, ListBox2.ListIndex - 1
End Sub


Private Sub OptionButton2_Click()
    m_clsLBoxAlign.Center ListBox1, ListBox2.ListIndex - 1
End Sub


Private Sub OptionButton3_Click()
    m_clsLBoxAlign.Right ListBox1, ListBox2.ListIndex - 1
End Sub


Private 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 Sub


Private Sub UserForm_Terminate()
    Set m_clsLBoxAlign = Nothing
End 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 (which is nowhere to be seen in above code snippets) and include multiple columns and change their alignment with radio buttons ?

Your help will be highly appreciated.