This week’s Tip of the Week will clearly demonstrate how you can dynamically set the Drop Down List Width of a Combo Box to the length of the longest item in its Row Source. The inspiration for this Tip came from one of our own resident Experts, mshmyob. In response to a Thread relating to this very Topic, mshmyob came up with a rather ingenious method to accomplish this task. He computed the Average Character Width of a String consisting of Upper and Lower case characters as well as the numbers 0 to 9, then applied this logic to determine the List Width of the Combo Box. This he did by calculating the length of the longest entry in the Combo Box, determined the current Font Name and Size, utilizing the TextWidth Method of a Report opened in Hidden Mode, computing the Average Character Width, then transferring this value back to the Form (to a Text Box), where the ListWidth of the Combo Box can now be calculated to accommodate the longest entry. What makes this approach unique is that:
I’ll post the code below for your preview. It is amply commented, even bordering on overkill, but it should give you a good indication of exactly what is happening. These code modifications have been made with mshmyob’s approval, but keep in mind that the original concept was his, not mind. Even with the varied comments, and what I feel is a sound and logical code based approach, I have made this Tip available as an Attachment. I strongly suggest that you download it and get a visual indication of how this functionality is actually implemented. Should you have any questions whatsoever, please feel free to ask them. I’ll be monitoring this Thread as I’m sure mshmyob will be also.
- This is all accomplished internally within Access with no external References or API Calls.
- Regardless of the Font Size and Name, the code will adjust and always produce the desired results.
- Since Forms do not have a TextWidth Method, an alternative method had to be realized in order to solve this dilemma. mshmyob’s use of the TextWidth Method within a Hidden Report, was not at all intuitive, and in fact was very clever.
I’ll post the code below for your preview. It is amply commented, even bordering on overkill, but it should give you a good indication of exactly what is happening. These code modifications have been made with mshmyob’s approval, but keep in mind that the original concept was his, not mind. Even with the varied comments, and what I feel is a sound and logical code based approach, I have made this Tip available as an Attachment. I strongly suggest that you download it and get a visual indication of how this functionality is actually implemented. Should you have any questions whatsoever, please feel free to ask them. I’ll be monitoring this Thread as I’m sure mshmyob will be also.
Code:
Private Sub Form_Open(Cancel As Integer) Dim lngNumberOfEntries As Long Dim lngLongestRowlength As Long Dim dblLargestCboEntryWidth As Double Call IntroDialog 'gcboCtl is a Global Object Variable of Type Combo Box 'Initialize it now Set gcboCtl = Me![cboAdjust] lngNumberOfEntries = gcboCtl.ListCount If lngNumberOfEntries = 0 Then Exit Sub 'No entries, Bye-Bye! 'Retrieve the longest Row of data from the Combo Box via the Function, pass 'to the Function the number of entries in the Combo Box lngLongestRowlength = fGetLongestRowLength(lngNumberOfEntries) 'Determine Font Size and Font Name in use and Save these values in Global 'Variables instead of passing Arguments to the Report, then retrieving them gintFontSize = gcboCtl.FontSize gstrFontName = gcboCtl.FontName 'Open the Report, but in Hidden Mode so you can apply Parameters to it such as 'FontSize and FontName. We need to use a Report because Access's TextWidth 'Method only works in a Report, and not a Form DoCmd.OpenReport "rptAvgString", acViewPreview, , , acHidden 'txtAvgValue holds the value passed back from the Report and is the Average 'Character Width based on the pre-determined String (strMessage) as defined 'in the Print() Event of the Report. You can hide the txtAvgValue Text Box, 'I just used this to show you how it works. Multiply the Average Character 'Width by the longest value in the Combo Box to arrive at the Length of the 'longest entry in Twips. dblLargestCboEntryWidth = Me.txtAvgValue * lngLongestRowlength 'Adjust the Drop Down List Width of the Combo Box gcboCtl.ListWidth = dblLargestCboEntryWidth 'Close the 'Hidden' Report DoCmd.Close acReport, "rptAvgString", acSaveNo Call FillDescription 'Program Description in Form Label End Sub
Code:
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim rpt As Report
Dim strMessage As String
Dim dblAvgSize As Double
Const conTWIPS As Byte = 1
Set rpt = Me
'Create a string with all upper and lowercase characters, This process will allow
'the creation of an'Average Characer Width in Twips given the Font's Name and Size.
strMessage = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789"
With rpt
'Set ScaleMode to Twips, and set FontName and FontSize properties.
'Character size is based on Font Size and Font Name - set as Twips not Pixels
.ScaleMode = conTWIPS
.FontName = gstrFontName
.FontSize = gintFontSize
End With
'Use the TextWidth method to determine the Horizontal Width - Vertical is unimportant.
'The value returned by the TextWidth method is expressed in terms of the coordinate system
'in effect for the Report, as defined by the Scale Method
dblAvgSize = rpt.TextWidth(strMessage) / 62
'Pass this number back to the calling form to calculate length needed
Forms!frmListWidth.txtAvgValue = dblAvgSize
End Sub
Comment