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

Posted on 2006-06-09
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.
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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 250 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 125 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 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 125 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 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 -
' EE   (°v°)
' Created       Jun-2006
' Credits       Inspired by a module by Stephen Lebans, to be found at
' 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

821 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