none
How to calculate the width of a access form textbox pending on font and length of characters string with VBA

    Question

  • Hi all,

    How to calculate the width of a access form textbox pending on font and length of characters string with VBA.

    If you have already for Tahoma font size 10 would be great...

    Thanks

    João

    Sunday, June 24, 2012 2:54 PM

Answers

  • The following function (thanks to http://cafeine.developpez.com/access/tutoriel/autoextensible/) computes the width of a text string in a control, using an undocumented Access function WizHook:

    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
        If Not Height Then
            GetTextLength = lx
        Else
            GetTextLength = ly
        End If
    End Function

    You can use it like this to "autofit" the width of a control:

    Public Sub AutoFit(ctl As Control)
        Dim lngWidth As Long
        lngWidth = GetTextLength(ctl, ctl.Value)
        ctl.Width = lngWidth + 40
    End Sub

    You can experiment with the value 40 - it is intended to leave enough room for the string.

    Call like this:

    AutoFit Me.txtLAstName

    or

    AutoFit Forms!frmTest!txtLastName


    Regards, Hans Vogelaar

    Sunday, June 24, 2012 3:25 PM

All replies

  • The following function (thanks to http://cafeine.developpez.com/access/tutoriel/autoextensible/) computes the width of a text string in a control, using an undocumented Access function WizHook:

    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
        If Not Height Then
            GetTextLength = lx
        Else
            GetTextLength = ly
        End If
    End Function

    You can use it like this to "autofit" the width of a control:

    Public Sub AutoFit(ctl As Control)
        Dim lngWidth As Long
        lngWidth = GetTextLength(ctl, ctl.Value)
        ctl.Width = lngWidth + 40
    End Sub

    You can experiment with the value 40 - it is intended to leave enough room for the string.

    Call like this:

    AutoFit Me.txtLAstName

    or

    AutoFit Forms!frmTest!txtLastName


    Regards, Hans Vogelaar

    Sunday, June 24, 2012 3:25 PM
  • Hans,

    very good.

    Thanks a lot!

    I adjusted from 40 to 200 because I'm using it in a label "etched"...

    Best Regards

    Sunday, June 24, 2012 8:55 PM