Column Width Class Module to Change Width of Columns in Listbox

rkaczano

Board Regular
Joined
Jul 24, 2013
Messages
77
Hi came across this elegant Class module for changing the width of listbox columns. See the link below and scroll to the bottom of the link.


I am struggling to understand how to make this work. I incorporated a Call statement in my code where I have a fully populated listbox. Based on the guidance I call the private functions and pass my Listbox1 to them (see blue text). It then passes text from my listbox to temporarry text boxes to figure out the largest width,

It fails within the second function on the line of code below with a Runtime 424 Error (Object Required):

If l_ColumnWidths.Count > lPosCol Then (See Purple Text below)

I see that the l_ColumnWidths collections is declared in Initialize event (See red text), and I assume the private function does not have access to the properties. But I am not sure why this is.


I have done the following:

1) created a Class module called "clsListCtrlWidths"
****************

2) Put the following code in the Class Module
Option Explicit
Public m_ColWidthMax As Long
*************

3) Put the following code in the Initialize Event
*****************
Private Sub Userform_Initialize()
Dim l_ColumnWidths As Collection
Set l_ColumnWidths = New Collection


Dim cRows As Long
Dim i As Long

cRows = Range("XLSummaryImportRange").Rows.Count '- Range("XLSummaryImportRange").Row + 1
ListBox1.ColumnCount = Range("XLSummaryImportRange").Columns.Count
'Populate the listbox.
With Me.ListBox1
For i = 1 To cRows
'Use .AddItem property to add a new row for each record and populate column 0
.AddItem Range("XLSummaryImportRange").Cells(i, 1)
'Use .List method to populate the remaining columns
.List(.ListCount - 1, 1) = Range("XLSummaryImportRange").Cells(i, 2)
.List(.ListCount - 1, 2) = Range("XLSummaryImportRange").Cells(i, 3)
Next i
End With
'Change width of columns in listbox using Class
Call AutoSizeColsWidth(Me.ListBox1)


lbl_Exit:
Exit Sub
End Sub
***************************

4) Put the following 2 functions in place
*****************
Private Function AutoSizeColsWidth(ByRef ctListCtrl As MSForms.ListBox)
Dim txtBoxDummy As control
Set txtBoxDummy = Me.Controls.Add("Forms.TextBox.1", "txtBoxDummy", False)
txtBoxDummy.AutoSize = True

Dim lRow As Long
Dim lCol As Long
Dim strColWidth As String

For lRow = 0 To ctListCtrl.ListCount - 1
For lCol = 0 To ctListCtrl.ColumnCount - 1
txtBoxDummy = ctListCtrl.List(lRow, lCol)
strColWidth = SetColWidth(strColWidth, txtBoxDummy, lCol)
Next lCol
Next lRow

ctListCtrl.ColumnWidths = strColWidth
End Function
**********************************
Private Function SetColWidth(stLen As String, ctCol1 As control, lPosCol As Long) As String
Dim stWidthTemp As String

If lPosCol > 0 Then
stWidthTemp = stLen & ";"
End If

Dim lTmpWidth As Long
Dim lColWidth As Long
lTmpWidth = ctCol1.Width
ctCol1.AutoSize = True
lColWidth = ctCol1.Width
ctCol1.AutoSize = False
ctCol1.Width = lTmpWidth

If l_ColumnWidths.Count > lPosCol Then (FAILS ON THIS LINE OF CODE)
If l_ColumnWidths.Item(lPosCol + 1).m_ColWidthMax < lColWidth Then
l_ColumnWidths.Item(lPosCol + 1).m_ColWidthMax = lColWidth
Else
lColWidth = l_ColumnWidths.Item(lPosCol + 1).m_ColWidthMax
End If
Else
Dim clsColWidth As clsListCtrlWidths
Set clsColWidth = New clsListCtrlWidths
clsColWidth.m_ColWidthMax = lColWidth
l_ColumnWidths.Add clsColWidth
End If

stWidthTemp = stWidthTemp & lColWidth
SetColWidth = stWidthTemp
End Function
*******************

Thanks
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
If the load of the listbox comes from the cells, as is your case. So you don't need a class, you can adjust the width of the Listbox columns with the following:


VBA Code:
Private Sub Userform_Initialize()
  Dim i As Long, rng As Range, anc As String
  
  Set rng = Range("XLSummaryImportRange")
  rng.EntireColumn.AutoFit

  With Me.ListBox1
    'get Column Width
    For i = 1 To rng.Columns.Count
      anc = anc & Int(rng.Cells(1, i).Width + 3) & "; "
    Next
    .ColumnWidths = anc
    .ColumnCount = rng.Columns.Count
    'Populate the listbox.
    For i = 1 To rng.Rows.Count
      .AddItem rng.Cells(i, 1)
      .List(.ListCount - 1, 1) = rng.Cells(i, 2)
      .List(.ListCount - 1, 2) = rng.Cells(i, 3)
    Next i
  End With
End Sub
 

rkaczano

Board Regular
Joined
Jul 24, 2013
Messages
77
Thanks - this does work.

Quick question. My data is yearly and the dates go left to right starting in columns 2 onward. The # years is variable.

The data fields are from rows 2 to 9 and not variable.

Your syntax works for the 3 years. I tried adjusting it so that the # years (columns) were dynamic and I cannot get it to work.

Effectively I put this in for 10 years:

.AddItem rng.Cells(i, 1)
.List(.ListCount - 1, 1) = rng.Cells(i, 2)
.List(.ListCount - 1, 2) = rng.Cells(i, 3)
.List(.ListCount - 1, 3) = rng.Cells(i, 4)
.List(.ListCount - 1, 4) = rng.Cells(i, 5)
.List(.ListCount - 1, 5) = rng.Cells(i, 6)
.List(.ListCount - 1, 6) = rng.Cells(i, 7)
.List(.ListCount - 1, 7) = rng.Cells(i, 8)
.List(.ListCount - 1, 8) = rng.Cells(i, 9)
.List(.ListCount - 1, 9) = rng.Cells(i, 10)

Next i

This populated the rows in the first column, all the dates but not data from column 4 onward.

I assume I must be interpreting the syntax incorrectly.

Thanks
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
What do you use the addItem method for?
If you don't have a specific reason to use it, then you can load directly from your named range like this:

VBA Code:
Private Sub Userform_Initialize()
  Dim i As Long, rng As Range, anc As String
  
  Set rng = Range("XLSummaryImportRange")
  rng.EntireColumn.AutoFit

  With Me.ListBox1
    For i = 1 To rng.Columns.Count
      anc = anc & Int(rng.Cells(1, i).Width + 3) & "; "
    Next
    .ColumnWidths = anc
    .ColumnCount = rng.Columns.Count
    .RowSource = rng.Address(external:=True)
  End With
End Sub

So if named range has 10 or 100 columns and 9 or 1000 rows, RowSource property loads all columns and all rows.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,613
Messages
5,625,848
Members
416,139
Latest member
MattBoard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top