Characters remaining

[RevJeff]

I have a txt box on a form that the user types a message into. But there needs to be a maximum of 150 characters in the text box. I am trying to create another text box that shows how many characters are left as the user types in the message. Text36 is the box where I want to show the remaining characters and Text31 is the box where the user is typing in the message.

This is what I have, but I don't know where to put it or even if this is how it should be done.

Text36 = 150 - (Len(Text31))

Thanks

 

[pbaldy]

You'd have to use the change event of the nebulously named Text31, and you'd need to use the Text property.

 

[isladogs]

And you may need to add the line Me.Text36.Requery afterwards

 

[pbaldy]

And you may need to add the line Me.Text36.Requery afterwards

Why would you, you're setting its value directly? I just tested, no requery is necessary.

 

[isladogs]

Good point. However, I did say 'may need to...'

 

[arnelgp]

supposed textbox Text0 is the one you want to limit by 150 characters.
the second textbox, Text2 will display how many characters remaining for input.

on the Load Event, show the remaining characters for input:

Private Sub Form_Load()
    Me.Text2 = 150 - Len(Me.Text0 & "")
End Sub

now on the Text0 (the textbox you want to limit), add code to it's
Change Event:

Private Sub Text0_Change()
    Call fLimitCharacters(Me.Text0, 150)
    Me.Text2 = 150 - Len(Me.Text0.text & "")
    
End Sub

you copy and the fLimitCharacters() function in a New Module in VBA.

Option Compare Database
Option Explicit

#If VBA7 Then
    #If Win64 Then
        Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" _
        (ByVal hWnd As LongPtr, ByVal wMsg As Long, ByVal wParam As Long, _
        lParam As Any) As Long
        Private Declare PtrSafe Function GetFocus Lib "user32" () As Long
    #Else
        Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" _
        (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, _
        lParam As Any) As Long
        Private Declare PtrSafe Function GetFocus Lib "user32" () As Long
    #End If
#Else
    Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
    (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, _
    lParam As Any) As Long
    Private Declare Function GetFocus Lib "user32" () As Long
#End If

Const EM_SETLIMITTEXT As Long = &HC5

' maybe called from the Change_Event of the textbox
' to limit
Public Function fLimitCharacters(ctl As control, lngLimit As Long)
    On Error GoTo Error_Handler
#If Win64 Then
    Dim hWnd As LongPtr
#Else
    Dim hWnd As Long
#End If
    Dim lngResult As Long
    Dim lngNewMax As Long
    'Get the handle of the current window
    hWnd = GetFocus()
    lngNewMax = Len(ctl & "")
    If lngNewMax < lngLimit Then
        lngNewMax = lngLimit
    End If
    SendMessage hWnd, EM_SETLIMITTEXT, lngNewMax, 0
Exit_Here:
    Exit Function
Error_Handler:
    MsgBox err.Number & ": " & err.Description
    Resume Exit_Here
End Function

 

[isladogs]

The simple code provided by pbaldy gives a character count successfully.
However, if you are typing a text string, it may not respond immediately.

Therefore, in this instance, there is merit in using the SendMessage API & a function as described by arnelgp as it updates instantly after each keystroke

In fact I have used almost identical code to that for over 15 years.
According to my database code notes it was originally
'From Access 97 Developer's Handbook
' by Litwin, Getz, and Gilbert (Sybex)
' Copyright 1997. All rights reserved.
' Modified by Stephen Lebans 1999

See http://www.lebans.com/limitcharsmemo.htm

Credit where credit is due ....

 

[arnelgp]

Fom planet-source-code.

 

[isladogs]

Just pointing out you should acknowledge your sources ....

 

[static]

Text31.InputMask = String(150, "C")

Shows exactly how many characters remaining and doesn't require any further code.

 

[arnelgp]

Yes it is. But there is an underline showing. It will be pleasing if all textbox have inputmask to be uniform or none at all for consistency.

 

[RevJeff]

Thank you all for your help.