Highlight Fields with more than 20 Charters

Number11

Member
Local time
Today, 05:58
Joined
Jan 29, 2020
Messages
624
Is there a way to highlight to the user that the text within a form field is greater than 20 Charters, so then they can reduce/abbreviate

The form is used as a final check to provide a report, the output Table has been set to the correct size 20. but this just cuts off any charters after the 30 so looking for a way that this can be addressed before the report is created.
 
Last edited:
Use Len() ?
Also we are talking characters, correct?
 
There are probably a few ways to do it.

One way is to loop through the controls and check the length. Use the tag property to identify which controls to check and the label caption to identify the field in a message box.

Code:
Sub TestLen(frm As Form, TagCharacter As String)
    Dim ctl As Control
    Dim flg As Boolean
    Dim strOut As String

    flg = True

    For Each ctl In frm.Controls

        If ctl.Tag = TagCharacter Then
            If Len(ctl.Value) > 30 Then
                flg = False
                ctl.BorderColor = vbRed
                strOut = strOut & Space(10) & "* " & ctl.Controls.Item(0).Caption & "  Length = " & Len(ctl.Value) & vbNewLine
            Else
                ctl.BorderColor = vbBlack
            End If

        End If
    Next

    If flg = False Then
        MsgBox "The following field(s) Exceed max limit of 30:" & vbNewLine & strOut
    End If

End Sub


maxlen.png
 
Try re-reading post #2 :( or look at moke123's code.
 
Your expression is comparing the contents of First_Name which should be text to 20 when you need to be looking at the length of the data in the textbox:

Len(First_Name) > 20
 
Your expression is comparing the contents of First_Name which should be text to 20 when you need to be looking at the length of the data in the textbox:

Len(First_Name) > 20
Tried that too it doesnt work :(
 
Tried that too it doesnt work :(
Well you are correct about that. :)

Perhaps try
Code:
Private Sub Text2_Change()
'Debug.Print Len(Me.Text2.Text) & " - " & Me.Text2.Text
If Len(Me.Text2.Text) > 20 Then
    'Debug.Print Len(Me.Text2)
    Me.Text2.ForeColor = vbRed
    Me.Text2.BackColor = vbYellow
Else
    Me.Text2.ForeColor = vbBlack
    Me.Text2.BackColor = vbWhite
   
End If

End Sub
which will do it immediately, else look at Moke's solution.
 
Last edited:
if this is in your conditional formatting, the field name needs to be in square brackets

if you look at it again you will see

Len("First_Name")>20

you should have

Len([First_Name])>20
 
if this is in your conditional formatting, the field name needs to be in square brackets

if you look at it again you will see

Len("First_Name")>20

you should have

Len([First_Name])>20
Well I cannot get it to work either?, with [] and even using .Text as well? :(
Not until I leave the control.
 
send a screenshot of your conditional formatting screen. I presume 'First_Name' is the correct spelling

However all this will do is highlight the issue, it won't require the user to correct it. Personally think Gasmans's suggestion is a better option for what you want to do
 

Users who are viewing this thread

Back
Top Bottom