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

  • 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
  • I would like to use the above but keep getting the error:

    Undefined function 'autofit' in expression

    any advice?

    Thanks

    Saturday, August 15, 2020 2:30 PM
  • Did you copy the code for GetTextLength and AutoFit from my previous reply into a standard module?

    Regards, Hans Vogelaar (https://www.eileenslounge.com)


    Saturday, August 15, 2020 2:57 PM
  • Hi Hans

    Yes I did

    {quote}

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


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

    Then in a query I inserted: StringWidth: AutoFit([Test])

    And get the error

    Thanks


    • Edited by TimTDP2 Sunday, August 16, 2020 7:59 AM
    Sunday, August 16, 2020 7:58 AM
  • AutoFit is a procedure, not a function. You cannot use it in a query.

    It is intended for use in the VBA code behind a form or report. For example, in the On Current event of a form:

    Private Sub Form_Current
        AutoFit Me.Test
    End Sub


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Sunday, August 16, 2020 8:55 AM
  • Okay, makes sense

    Let me explain what I am trying to do..........

    In a report I want to "strikethrough" text in a control. Access doesn't allow for this, not that I am aware of.

    So what I have tried is to put another control over the control and set it's control source:=String(Len([Equipment]),"-")

    But the font width of - is not the same as the letters of the alphabet, so it doesn't look 100% correct. 

    Which is why I to calculate the width of a access form textbox pending on font and length of characters string with VBA. I can also calculate the width, divide one into the other, so I then know who many - to use

    Hope this makes sense!

    Thanks

    Sunday, August 16, 2020 9:16 AM
  • You can use the GetTextLength function for this purpose. For example:

        Dim w As Long
        w = GetTextLength(Me.Equipment, Me.Equipment.Value)

    w will be the length of the value of the Equipment control (in twips, where 1 inch = 1440 twips)


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Sunday, August 16, 2020 9:50 AM
  • Thanks very much
    Sunday, August 16, 2020 10:02 AM
  • Thanks very much. I can do what I want to. Great!

    How can I use this function inside a function and call it from a query?

    Public Function GetTextWidthInTwisps(ctlControl As Control, strText As Variant) As Long
    If Len("" & strText) < 1 Then Exit Function
    GetTextWidth = GetTextLength(ctlControl, strText)
    End Function

    I can put controls on the report and use GetTextLength. But how can I use it in a query, rather than make the report messy with additional controls?

    Monday, August 17, 2020 9:48 AM
  • What would you want to do with this in a query?

    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Monday, August 17, 2020 11:29 AM
  • So to get the solution to work, I have the following controls in a report:

    Equipment - comes from table that is the report's record source 

    lngWidthOfEquipment  =GetTextLength([Equipment ],[Equipment ].[Value])

    txtStrikeThrough = "-"

    lngWidthOfStrikeThrough = =GetTextLength([txtStrikeThrough],[txtStrikeThrough].[Value])

    txtStrikeThroughLength = String(=[lngWidthOfEquipment  ]/[lngWidthOfStrikeThrough],"-")

    txtStrikeThroughLength is the same length as the text in equipment, so I get a perfect strike through effect

    Rather than have the controls lngWidthOfEquipment , lngWidthOfStrikeThrough, txtStrikeThrough, txtStrikeThroughLength on the report, I would like the calculations done in a query, and then make the query the reports record source


    Monday, August 17, 2020 11:58 AM
  • I don't think that is feasible - you cannot refer to a control as a control AND to that control's value in a query.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Monday, August 17, 2020 1:58 PM
  • Thank you very much
    Monday, August 17, 2020 3:29 PM