Text Field "Overflow" (Needs to be scrolled) indicator

Posted on 2006-06-09
Medium Priority
Last Modified: 2011-10-03
Is there a way to detect whether a vertically scrollable text field on a form has text that is not shown (i.e., you need to scroll to see it?)

I've got a situation in which a lot of these fields will be visually scanned, and I would like to be able to perform a test and show/hide a little red rectangle next to the lower right corner of the field if there are lines that need to be scrolled to.

I cleverly built the subform these text fields are on so it is exactly 3 lines high, which effectively hides any 4th line completely. I could make the form a little taller, so that a half line would show...but some kind of test and the little red indicator would be more certain.

Any guidance on this would be appreciated.

Question by:codequest
  • 4
  • 2
  • 2
  • +1
LVL 34

Expert Comment

ID: 16875025
You can use a recordcount function and find the number of records that might appear on the subform, then subtract the number of lines you can display, and if the diff is >0 then your box is red.....something like that?
LVL 34

Expert Comment

ID: 16875090
I think I see what you've done...although I don't know why you would want to do it that way. But if your textbox is 1" wide, that means it can display about 15 characters, multiply that times 4 if your box is that tall, and that's how many characters you can display before you need to scroll. So in light of that, each string would have to be character counted and divided by 60 or so to find your "TRUE" for your box.

WHERE LEN(YourVarcharColumnName) > 60

Author Comment

ID: 16875107
Thanks for the input.  I was hoping there might be a way besides character count, because I use a lot of line returns in the text field.   If the calc is too complicated I'll just make the text field a little taller so it shows a partial line.

I'll leave this out there a bit and see if there are any other suggestions.

Good prompt; 'preciate it.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

LVL 34

Expert Comment

ID: 16875159
Well playing with this...this works with the forms On_Current event. You'll need to do the whole focus thing for each control. Maybe a loop to get all of them with one loop.

Me.Text4.SetFocus    '<-------Your text box
If Len(Me.Text4.Text) > 10 Then
Me.Box7.Visible = True    '<-------------Your red box
Me.Box7.Visible = False
End If

LVL 34

Accepted Solution

jefftwilley earned 1000 total points
ID: 16875190
Chr(13) is Carriage Return
Chr(10) is Line feed

Me.Text4.SetFocus    '<-------Your text box
strVal = Me.Text4.Text
If Len(strVal) > 10 Or InStr(strVal, Chr(13)) Or InStr(strVal, Chr(10)) Then  '<---looks for line feed or CR
Me.Box7.Visible = True    '<-------------Your red box
Me.Box7.Visible = False
End If
LVL 58

Assisted Solution

harfang earned 500 total points
ID: 16875366
Hello codequest

If you don't want to count characters – which is widely inaccurate, you will have to ask Windows to draw the text for you, and then study the resulting size... Lebans is good with that sort of things, and you can study his code:

NEW  AutoSizeTextBox.zip Ver 3.0 is a database containing functions to automatically resize a TextBox control to fit the current fields contents.

The code can easily be tweaked so that is draws in a fixed width, and you can examine the resulting text height to count the lines it will require. However, the scroll bar is missing from his sample. He shows elsewhere on his site how to obtain the scrollbar width (set by the user preferences).

All in all, it would require a few hours of intense head-scratching, but it can be done.

LVL 38

Assisted Solution

puppydogbuddy earned 500 total points
ID: 16875485
Hello codequest,

I found the code below on one of the public forums last year, and kept it for potential future use. According to the postee, it provides two different procs that auto-scroll the most recently added line into view.  It is supposed to be particularly useful for displaying logging information in a RichTextBox or memo control. I would try the Windows API Auto Function first.  If that doesn't work for you, then try the second one.  Hope this helps.  

<<<Herfried K. Wagner [MVP]>>>
If you want to scroll to the end of the text, there are two ways to
accomplish it:
Private Const WM_VSCROLL As Int32 = &H115
Private Const SB_BOTTOM As Int32 = 7

Private Declare Auto Function SendMessage Lib "user32.dll" ( _
    ByVal hwnd As IntPtr, _
    ByVal wMsg As Int32, _
    ByVal wParam As Int32, _
    ByVal lParam As Int32 _
) As Int32

Private Sub AddLine(ByVal Destination As RichTextBox, ByVal Text As String)
    With Destination
        .AppendText(Text & ControlChars.NewLine)
        SendMessage(Destination.Handle, WM_VSCROLL, SB_BOTTOM, 0)
    End With
End Sub

Author Comment

ID: 16877550
Thanks for inputs.   I'll ponder these...though I'm seeing that if it's easy it may not be accurate, and if it's more accurate it's not very easy...so I may decide it's not worth pursuing.   I appreciate the info, though.
LVL 58

Expert Comment

ID: 16878031
Hello codequest

Appears you accepted too quickly... ;)

I had some time and your problem was intriguing, so I did as I suggested. Here is the result, I think it does exactly what you need: check for "overflow" to give some visual clue on your form.

' Module        basTextboxOverflow
' Copyright     Markus Fischer, Geneva, 2006
' Author        Markus G Fischer - www.harfang.ch
' EE            www.experts-exchange.com/M_94825.html (°v°)
' Created       Jun-2006
' Credits       Inspired by a module by Stephen Lebans, to be found at
'               http://www.lebans.com/autosize_textbox.htm
' Distribution  free if proper credit given
' Purpose
' ¯¯¯¯¯¯¯
' Implements a single function:
'   TextBoxOverflow(ptxtBox As TextBox) As Boolean
'       ptxtBox:    TextBox, normally multi-line memo control
'       Returns:    True if the text in the control does not fit
'                   i.e. it overflows, the scrollbar is needed
' Usage
' ¯¯¯¯¯
' Call for example in form events:
'   Private Sub Form_Current()
'       txtMemo_AfterUpdate
'   End Sub
'   Private Sub txtMemo_AfterUpdate()
'       lblContinued.Visible = TextBoxOverflow(Me.txtMemo)
'   End Sub
Option Compare Database
Option Explicit

' WinAPI Constants, types and declares...

' Get, release and examine a display device context:
Private Declare Function GetDC Lib "user32" _
    (ByVal hWnd As Long) As Long
Private Declare Function ReleaseDC Lib "user32" _
    (ByVal hWnd As Long, ByVal hDC As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" _
    (ByVal hDC As Long, ByVal nIndex As Long) As Long

' needed subfunctions for the logical screen resolution
Private Const LOGPIXELSX = 88
Private Const LOGPIXELSY = 90

' logical font information:
Private Const LF_FACESIZE = 32
Private Type LOGFONT
    lfHeight As Long
    lfWidth As Long
    lfEscapement As Long
    lfOrientation As Long
    lfWeight As Long
    lfItalic As Byte
    lfUnderline As Byte
    lfStrikeOut As Byte
    lfCharSet As Byte
    lfOutPrecision As Byte
    lfClipPrecision As Byte
    lfQuality As Byte
    lfPitchAndFamily As Byte
    lfFaceName As String * LF_FACESIZE
End Type

' Creation of a font, setting and delting font objects
Private Declare Function CreateFontIndirectA Lib "gdi32" _
    (lpLogFont As LOGFONT) As Long
Private Declare Function SelectObject Lib "gdi32" _
    (ByVal hDC As Long, ByVal hObject As Long) As Long
Private Declare Function DeleteObject Lib "gdi32" _
    (ByVal hObject As Long) As Long

' windows metrics informations (for scrollbar width)
    cbSize As Long
    iBorderWidth As Long
    iScrollWidth As Long
    iScrollHeight As Long
    iCaptionWidth As Long
    iCaptionHeight As Long
    lfCaptionFont As LOGFONT
    iSMCaptionWidth As Long
    iSMCaptionHeight As Long
    lfSMCaptionFont As LOGFONT
    iMenuWidth As Long
    iMenuHeight As Long
    lfMenuFont As LOGFONT
    lfStatusFont As LOGFONT
    lfMessageFont As LOGFONT
End Type

' call for the metrics above
Private Declare Function SystemParametersInfoA Lib "user32" ( _
    ByVal uAction As Long, _
    ByVal uParam As Long, _
    ByRef lpvParam As Any, _
    ByVal fuWinIni As Long _
    ) As Long
' needed subfunction:

' Standard rectangle information
Private Type RECT
    Left As Long
    Top As Long
    Right As Long
    Bottom As Long
End Type

' Draw text in a given DC on a given rectangle...
Private Declare Function DrawText Lib "user32" Alias "DrawTextA" ( _
    ByVal hDC As Long, _
    ByVal lpStr As String, _
    ByVal nCount As Long, _
    lpRect As RECT, _
    ByVal wFormat As Long _
    ) As Long

' needed arguments for wFormat
Public Const DT_WORDBREAK = &H10
Public Const DT_CALCRECT = &H400

' Only exported function:
Function TextBoxOverflow(ptxtBox As TextBox) As Boolean
' Simply returns True if the textbox .Value cannot be displayed using
' the current .Width, .Height, and .Scrollbars.
' Adjusts the actual metrics for control border style, and gets the width
' of the scrollbar if it is activated.
    Dim rectCtl     As RECT         ' the control's rectangle
    Dim hWnd        As Long         ' form's window
    Dim hDC         As Long         ' device context
    Dim dblTPPX     As Double       ' twips per pixel X
    Dim dblTPPY     As Double       ' twops per pixel Y
    Dim lfNew       As LOGFONT      ' font information
    Dim hNewF       As Long         ' new font structure pointer
    Dim hOldF       As Long         ' old font structure pointer
    Dim ncmWin      As NONCLIENTMETRICS
    Dim lngScrollW  As Long         ' width of the scrollbar
    Dim lngBorderX  As Long         ' border (horizontally)
    Dim lngBorderY  As Long         ' border (vertically)

    ' False if control is null
    If IsNull(ptxtBox) Then Exit Function
    ' Get logical screen resolution
    hDC = GetDC(0&)
    dblTPPX = 1440 / GetDeviceCaps(hDC, LOGPIXELSX)
    dblTPPY = 1440 / GetDeviceCaps(hDC, LOGPIXELSX)
    ReleaseDC 0&, hDC
    ' guesstimate border widths
    With ptxtBox
        lngBorderX = 2
        lngBorderY = 2
        If .SpecialEffect >= 1 And .SpecialEffect <= 3 Then
            ' raised, sunken on chiseled
            lngBorderX = lngBorderX + 2
            lngBorderY = lngBorderY + 2
        ElseIf .BorderWidth > 0 And (.SpecialEffect = 4 Or .BorderStyle) Then
            lngBorderX = lngBorderX + .BorderWidth * 1440 / 72 / dblTPPX
            lngBorderY = lngBorderY + .BorderWidth * 1440 / 72 / dblTPPY
        End If
    End With
    ' get default scrollbar width (if needed)
    If ptxtBox.ScrollBars Then
        ncmWin.cbSize = 340
        SystemParametersInfoA SPI_GETNONCLIENTMETRICS, 0&, ncmWin, 0&
        lngScrollW = ncmWin.iScrollWidth
    End If
    ' prepare font information from control
    With lfNew
        .lfHeight = -(ptxtBox.FontSize * 1440 / 72 / dblTPPY)
        .lfWeight = ptxtBox.FontWeight
        .lfItalic = ptxtBox.FontItalic
        .lfUnderline = ptxtBox.FontUnderline
        .lfFaceName = ptxtBox.FontName & vbNullChar
    End With
    ' get width of control in pixel
    rectCtl.Right = ptxtBox.Width / dblTPPX - lngBorderX - lngScrollW
    ' get form's device display context, create font and set
    hDC = GetDC(ptxtBox.Parent.hWnd)
    hNewF = CreateFontIndirectA(lfNew)
    SelectObject hDC, hNewF
    ' draw the text in the available width
    DrawText hDC, ptxtBox.Value, -1, rectCtl, DT_CALCRECT + DT_WORDBREAK
    ' return true if there is not enough vertical room...
    TextBoxOverflow = ptxtBox.Height / dblTPPY < rectCtl.Bottom + lngBorderY
    ' restore prior font, delete our temp font.
    SelectObject hDC, hOldF
    DeleteObject hNewF
End Function



Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question