Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

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 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 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

721 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