Michaels
Active Member
- Joined
- Apr 2, 2009
- Messages
- 404
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:
Userform Code:
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.
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:
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
Userform Code:
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.