I’m revisiting my ListBox control work from these two articles:

Access ListBox Row Source and Type | Access JumpStart

ListBox Column size adjustments | Access JumpStart

The second article discsusses how to adjust column sizes using the ColumnWidths property and the Join and Split functions to place the values in an array, updating each column width in twips.

Gotta love twips. They are the default measurement in Access for spacing and sizing things like forms and controls. Go to the trusty Wikipedia to get more info than you’d ever want to know about what a twip is:

Twip – Wikipedia

In my example code I am referencing two functions. Here’s a call to those functions getting the larger of my minimum desired twips and the longest string of characters in a particular column of the list:

Max(MinWidth, GetLongestStringTwips("CustomerName"))

Here I will discuss the function GetLongestStringTwips. I am passing in a column name.

Let’s look at the function:

Private Function GetLongestStringTwips(ColumnName) As Long
    Dim retVal As Long
    Dim hTwips As Long
    Dim LongestString As String
    LongestString = Nz(NiceDLookup("TOP 1 " & ColumnName, "tmpImport_FlatFile_List", "Len(Nz(" & ColumnName & ")) = (SELECT Max(Len(Nz(" & ColumnName & "))) FROM tmpImport_FlatFile_List WHERE " & ColumnName & " NOT LIKE '*:FORECAST QUALIFIER*' )"))
    If LongestString = "" Then LongestString = ColumnName & "  "
    LongestString = Replace(LongestString, ":FORECAST QUALIFIER,FORECAST TIMING QUALIFIER", "")
    HALutil.getTextTwips LongestString, retVal, hTwips, Me.FileList.FontName, Me.FileList.FontSize, Me.FileList.FontWeight, Me.FileList.FontItalic, Me.FileList.FontUnderline
    retVal = retVal + 150
    GetLongestStringTwips = retVal
End Function

The majority of this function is simply queries to get the longest string in the temporary table column with the name passed the function. Once we have the string with the greatest length (although to be fair, this isn’t necessarily the longest string… 3 W’s “WWW” are wider than 4 i’s “iiii”), but as a spitball approach we will get generally close using the string with the longest length in an average data set.

The longest text then gets sent to the HALutil.getTextTwips function to calculate and return the twips. Notice that we are passing many things to this library function which all affect the actual width of the string. The font, the font size, the font weight, whether the font is italicized and finally whether the font is underlined. All info we can pull from the control.

Let’s see what the library function looks like:

' ----------------------------------------------------------------
' Procedure Name: getTextTwips
' Purpose: Get width and height of Text in TWIPS
' Procedure Kind: Function
' Procedure Access: Public
' Parameter strText (String): Text to be processed (or blank)
' Parameter dWidth (Long): width in TWIPS returned here
' Parameter dHeight (Long): height in TWIPS returned here
' Parameter FontName (String): Font name
' Parameter FontSize (Long): Font size in points
' Parameter FontWeight (fwFontWeight): Font weight value
'              Optional, Default = fwNormal
' Parameter FontItalic (Boolean): Is Font Italic?
'              Optional, Default = False
' Parameter FontUnderline (Boolean): Is Font Underlined?
'              Optional, Default = False
' Parameter Cch (Long): Alternate number of characters to process
'              Optional, Default = 0
' Parameter MaxWidthCch (Long): Max width if character count is used
'              Optional, Default = 0
' Return Type: Boolean
' Explanation: (based upon a description of WizHook.TwipsFromFont()
'               in https://team-moeller.de)
'  1. Normally, the text from the parameter [strText] is used for
'     the calculation.
'  2. If the [Cch] parameter is set to a value other than 0, the text
'     in the [strText] parameter is ignored. Instead, the [Cch]
'     parameter specifies the number of average width characters
'     for the calculation.
'  3. The value specified in the [MaxWidthCch] parameter is considered
'     only if the [Cch] parameter has a value other than 0. In this case,
'     the number from the [MaxWidthCch] parameter is considered as a
'     maximum width for the calculation. The total number of
'     characters in this case is in the parameter [Cch].
'     [Cch] - [MaxWidthCch] characters with average width and
'     [MaxWidthCch] characters with maximum width are then taken into account.
'     If [MaxWidthCch] is larger than [Cch], then [Cch] characters with
'     maximum width are taken into account.
' Author: steve
' Date: 9/30/2021
' ----------------------------------------------------------------
Public Function getTextTwips(strText As String, ByRef dWidth As Long, ByRef dHeight As Long, FontName As String, FontSize As Long, _
   Optional FontWeight As fwFontWeight = fwNormal, Optional FontItalic As Boolean = False, Optional FontUnderline As Boolean = False, _
   Optional Cch As Long = 0, Optional MaxWidthCch As Long = 0 _
   ) As Boolean
   Dim dx As Long, dy As Long
   On Error GoTo ProcError
   WizHook.Key = WizKey
   getTextTwips = WizHook.TwipsFromFont(FontName, FontSize, CLng(FontWeight), FontItalic, FontUnderline, Cch, strText, MaxWidthCch, dx, dy)
   ' workaround for WizHook return of unsigned Integer values up to 65535
   dWidth = CLng("&h0000" & Right(Hex(dx), 4))
   dHeight = CLng("&h0000" & Right(Hex(dy), 4))
ProcExit:
   Exit Function
ProcError:
   HALutil.LibErrHandler "HALutil", "getTextTwips", "RETURN"
   getTextTwips = False
   Resume ProcExit
End Function

The kind wizard builders on the Microsoft Access team left us with a nice undocumented “Wizhook” object we can access with some nice functions in it, one of which returns twips of a text string.

Here is a link to the German site referred to in our comments. Use your browser to translate to your favorite language: Willkommen bei Team-Moeller.de – Wizhook-Objekt

That is the secret there. A nice black box hidden in the undocumented recesses of Access. Ooooh…

Given that funtion, we get the desired twips of the defined string and then end up adding another 150 twips to give us some right hand margin in the column.