Version 1.1 First Published 10 Nov 2022 Last Updated 27 Nov 2022
This article discusses different methods of ensuring all text is visible on a form textbox control.
When text varies significantly in length between records, there are three simple approaches to ensuring all the text is automatically made visible:
a) adjust the width of the textbox to fit the text
b) adjust the font size to fit the control width
c) use a zoom box
The first two methods are easily achieved using the hidden, undocumented WizHook object.
For more details, see my article: The WizHook Object - Hidden, Undocumented . . . But Very Useful
a) Automatically adjust the textbox width to fit the text
Return To Top
This approach was suggested in a post at MSDN forums in 2012:
Calculate the width of a access form textbox pending on font and length of characters string with VBA
This approach is probably better suited to a single form but for convenience, I anm using a continuous form to show the changes more easily
Place this code in a standard module
CODE:
Option Compare Database
Option Explicit
'-----------------------------------------------
Public Function GetTextLength(pCtrl As Control, ByVal str As String, Optional ByVal Height As Boolean = False)
Dim lx As Long, ly As Long
' Initialize WizHook
WizHook.Key = 51488399
' Populate the variables lx and ly with the width and height of the
' string in twips, according to the font settings of the control
WizHook.TwipsFromFont pCtrl.FontName, pCtrl.FontSize, pCtrl.FontWeight, _
pCtrl.FontItalic, pCtrl.FontUnderline, 0, str, 0, lx, ly
'check whther Height option used
If Not Height Then
GetTextLength = lx
Else
GetTextLength = ly
End If
End Function
'-----------------------------------------------
'Code by Hans Vogelaar
'https://social.msdn.microsoft.com/Forums/en-US/2727e4a4-57a3-4e4d-a20a-314464579ad3/how-to-calculate-the-width-of-a-access-form-textbox-pending-on-font-and-length-of-characters-string?forum=isvvba
Public Sub AutoFit(ctl As Control)
'Experiment with the value 80 - it is intended to leave enough room for the string.
'Typical usage:
'AutoFit Me.txtLastName or AutoFit Forms!frmTest!txtLastName
Dim lngWidth As Long
lngWidth = GetTextLength(ctl, ctl.Value)
ctl.Width = lngWidth + 80
End Sub
Next add this code to the Form_Load event and the double click event of the form textbox
NOTE: replace 'TextboxName' with the actual name of your textbox control
The textbox width will be automatically adjusted when the user double-clicks the control
CODE:
Private Sub Form_Load()
'autofit text on first record
AutoFit Me.TextboxName
End Sub
Private Sub TextboxName_DblClick(Cancel As Integer)
'autofit text on double click
If Len(Me.Me.TextboxName) > 0 Then AutoFit Me.Me.TextboxName
End Sub
NOTE:
If preferred, the Form_Current eventcan be used instead of the textbox double-click event.
This may work better when using single forms but it can be distracting when trying to just edit the text
The method can work well if the text is of a similar length in all records. However, it can be problematic if some records are very long. For example:
For that reason, I do NOT recommend using this approach with a long text (memo) datatype.
b) Automatically adjust the font size to fit the control
Return To Top
This approach was suggested in a post at Access World Forums in 2018: Shrink to fit or AutoResizeFont
Once again, this approach is probably better suited to a single form. For convenience, I am using a continuous form to show the changes more easily
Use this code in a standard module. The GetTextLength function is exactly the same as in the previous approach
CODE:
Option Compare Database
Option Explicit
Public Const DefaultFontSize = 11 'modify as appropriate
'-----------------------------------------------
Public Function GetTextLength(pCtrl As Control, ByVal str As String, Optional ByVal Height As Boolean = False)
Dim lx As Long, ly As Long
' Initialize WizHook
WizHook.Key = 51488399
' Populate the variables lx and ly with the width and height of the
' string in twips, according to the font settings of the control
WizHook.TwipsFromFont pCtrl.FontName, pCtrl.FontSize, pCtrl.FontWeight, _
pCtrl.FontItalic, pCtrl.FontUnderline, 0, str, 0, lx, ly
'check whther Height option used
If Not Height Then
GetTextLength = lx
Else
GetTextLength = ly
End If
End Function
'-----------------------------------------------
'Code by Colin Riddington AKA Isladogs on Access
Public Sub AutoFontSize(ctl As Control)
'Typical usage:
'AutoFontSize Me.txtLastName or AutoFontSize Forms!frmTest!txtLastName
Dim lngWidth As Long
lngWidth = GetTextLength(ctl, ctl.Value)
If lngWidth > ctl.Width Then
ctl.FontSize = Int(ctl.FontSize * (ctl.Width / lngWidth))
'Debug.Print ctl.FontSize
Else
'ctl.FontSize = Int(ctl.FontSize / (ctl.Width / lngWidth))
'revert to default size
ctl.FontSize = DefaultFontSize
End If
End Sub
Next add this code to the form replacing 'TextboxName' with the actual name of your textbox control
The font size will be automatically adjusted when the user double-clicks the control
CODE:
Private Sub Form_Current()
'restore default size
Me.TextboxName.FontSize = DefaultFontSize
End Sub
'-----------------------------------------------
Private Sub Form_Load()
'set default size
Me.TextboxName.FontSize = DefaultFontSize
End Sub
'-----------------------------------------------
Private Sub TextboxName_DblClick(Cancel As Integer)
'reduce font size to fit all text in the textbox
If Len(Me.TextboxName) > 0 Then AutoFontSize Me.TextboxName
End Sub
Once again, this method can work well if the text is of a similar length in all records.
However, it will result in tiny, unreadable text if some records are very long. For example:
Can you read that? In extreme cases, the font size can shrink to 1pt
For that reason, I do NOT recommend this method for most situations.
c) Use a Zoom Box
Return To Top
Access provides a built-in zoom box which provides an easier method of achieving similar results with no code required.
To open a zoom box, press Shift+F2 on the keyboard when the control is selected. The text can be edited in the zoom box.
If you have Access 365, the font size and the zoom box dimensions can both be adjusted.
For more information, see the Microsoft help article Access World Forums in 2018: Use the zoom box to enter text and expressions
If preferred, code can be used to open the zoom box automatically when the control is double-clicked. This is VERY simple!
CODE:
Private Sub TextboxName_DblClick(Cancel As Integer)
'open zoom box
DoCmd.RunCommand acCmdZoomBox
End Sub
The zoom box normally appears in the centre of the screen and may cover the textbox itself
However, you can use code to place the zoom box in a precise location e.g. just under the selected textbox.
This works in both single and continuous forms and in subforms
For full details on how this is done, see my article: Accurately Move Forms and Controls
YouTube Videos
Return To Top
I have created a YouTube video for my Isladogs on Access channel demonstrating the use of the WizHook TwipsFromFont function.
This is now available at: Using WizHook - Twips From Font function or you can click below:
In addition, you can watch my previous YouTube video on the same channel which is an introduction to Wizhook and a few of its simpler functions.
This is now available at: Wizhook: A Hidden (But Very Useful) Access Object or you can click below:
Further videos on WizHook are also planned
If you subscribe to my Isladogs on Access channel on YouTube, you will be notified whenever new videos are released.
Download
Return To Top
Click to download the example app including the above code:
Autofit Text ACCDB file - 0.5 MB (zipped)
Summary
Return To Top
The first two approaches using Wizhook.TwipsFromFont do have their uses, but in most cases using a Zoom box is both simpler and more effective.
Please use the contact form to let me know whether you found this article & example app useful or if you have any questions.
Colin Riddington Mendip Data Systems Last Updated 27 Nov 2022
|
Return to Code Samples Page
|
Return to Top
|