In a previous article, I discussed a listbox conundrum I had with really long lists:

Access ListBox Row Source and Type | Access JumpStart

One of the things I wanted to do with this listbox was adjust the column sizes of the list on the fly, depending on the size of the contents.

The first piece of this, the column sizes themselves, can be adjusted via VBA, but not really easily individually. I believe with the way I am using the ListBox to pull from a query, I think I need to use the ColumnWidths column, although there might be a way to use the singular ColumnWidth property, although I think that’s actually for datasheets.

Anyway, I found that using the ListBox.ColumnWidths property worked pretty well in combination with the Split and Join VBA functions. I have a fixed number of columns, and I have a default set of widths specified. These column widths are translated to a string of numbers representing column widths by twips when you retrieve the property in VBA.

Using Split, I transform this VBA string representation into an array and then I can change each element to the number of twips I want, then use Join on the array to put everything back into a string and assign it to the ListBox control. Here is the function I am using (specific to my app) to adjust column widths:

Private Sub FileList_UpdateColWidths()
    Dim FLwidths As Variant
    Const MinWidth As Long = 600
    FLwidths = Split(Me.FileList.ColumnWidths, ";")
    FLwidths(10) = Max(MinWidth, GetLongestStringTwips("LastModified"))
    FLwidths(2) = Max(MinWidth, GetLongestStringTwips("CustomerName"))
    FLwidths(3) = Max(MinWidth, GetLongestStringTwips("FileType"))
    FLwidths(4) = Max(MinWidth, GetLongestStringTwips("ForecastIdentifiers"))
    FLwidths(9) = Max(MinWidth, GetLongestStringTwips("FileName"))
    FLwidths(1) = Max(MinWidth, GetLongestStringTwips("StatusMessage"))
    FLwidths(5) = Max(MinWidth, GetLongestStringTwips("StartDate"))
    FLwidths(6) = Max(MinWidth, GetLongestStringTwips("EndDate"))
    FLwidths(11) = Max(MinWidth, GetLongestStringTwips("FilePath"))
    FLwidths(8) = Max(MinWidth, GetLongestStringTwips("FileSize"))
    FLwidths(7) = Max(MinWidth, GetLongestStringTwips("ProcessedDate"))
    Me.FileList.ColumnWidths = Join(FLwidths, ";")
End Sub

This technique works great, sets all the columns at the same time, (rather than adjusting each one individually). I am using two more custom functions to check each column in my result set and return the maximum value of my MinWidth constant of 600 twips and a calculated number of twips based on the field with the greatest Length of characters.

I’ll get into those functions in a later article. I can’t believe VBA didn’t have a simple mathematical “Max” function, but it doesn’t and I created one of my own. The GetLongestStringTwips function is querying the column for the string with the biggest “Len” and then passing that to another function that calculates the twips of that string based on font characteristics like size, and family. So you could be just setting static column widths here with ease. Note that my first column would be FLwidths(0) but I have that bound and hidden, so I never need to adjust that column size.