Thursday, January 19, 2017

Excel VBA ListView control columns auto-fit (auto-resize)

Briefly, the subroutine code at the end of the page can be used to auto-resize (auto-fit) columns for Microsoft List View control used in user-forms in Excel, PowerPoint, Word, and the rest of MS Office tools. It is a simple, easy to understand, and short method to do this job. It can work with different font types and sizes.

The following pictures shows an actual example for list view control before and after the auto-resize subroutine (data source of table contents from: )

Column width is previously set to 100

Column width is auto-fit

' A procedure to auto-resize column width of list view control

Sub AutoResizeListView(MyListView As Variant)
' Create a dynamic label and set it invisible

Set MyLabel = Me.Controls.Add("Forms.Label.1", "Test Label", True)
With MyLabel

    .Font.Size = MyListView.Font.Size

    .Font.Name= MyListView.Font.Name

    .WordWrap = False

    .AutoSize = True

    .Visible = False

End With
' Auto-resize the first column

MyLabel.Caption = MyListView.ColumnHeaders(1).Text

MaxColumnWidth = MyLabel.Width
For i = 1 To MyListView.ListItems.Count

MyLabel.Caption = MyListView.ListItems(i).Text

If MyLabel.Width > MaxColumnWidth Then

MaxColumnWidth = MyLabel.Width

End If

Next i

MyListView.ColumnHeaders(1).Width = MaxColumnWidth + 8
' Auto-resize the rest of columns

For i = 1 To MyListView.ColumnHeaders.Count - 1

MyLabel.Caption = MyListView.ColumnHeaders(i + 1).Text

MaxColumnWidth = MyLabel.Width

For j = 1 To MyListView.ListItems.Count

MyLabel.Caption = MyListView.ListItems(j).SubItems(i)

If MyLabel.Width > MaxColumnWidth Then

MaxColumnWidth = MyLabel.Width

End If

Next j     'Next cell row in the current column

MyListView.ColumnHeaders(i + 1).Width = MaxColumnWidth + 8   
Next i     'Next column
Me.Controls.Remove MyLabel.Name    'Remove the dynamic label

End Sub

This sub can be called using the following method inside the user-form code

Call AutoResizeListView(ListViewName)