Userform Textbox Char counter

I would like to add a character counter in a userform that monitors text going into a textbox.  I have a limit of number that can be used.. and I would like user to know how many they have left as they are typing in the textbox.  Is this possible?  If so, what would go in the: Private Sub Textbox1_Change() that would link to a Label that would chg..
RWayneHAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Place the following codes on UserForm Module.

Const MaxCharLength As Integer = 20

Private Sub TextBox1_Change()
If Len(Me.TextBox1) < MaxCharLength Then
    Me.cmdOK.Caption = "Character remains: " & MaxCharLength - Len(Me.TextBox1)
Else
    Me.cmdOK.Caption = "OK"
    Me.TextBox1.Value = Left(Me.TextBox1.Value, MaxCharLength)
End If
End Sub

Private Sub UserForm_Initialize()
Me.cmdOK.Caption = "Character remains: " & MaxCharLength
End Sub

Open in new window

TextBox-CharLimitCheck.xlsm
0
 
ShumsDistinguished Expert - 2017Commented:
Hi RWayneH,

You can try something like below, change the TextBox & Label as required:
Private Sub TextBox1_Change()
Dim RemText As Integer, TextCount As Integer
TextCount = 10
RemText = TextCount - TextBox1.TextLength
Label1.Caption = RemText
End Sub

Open in new window

0
 
Roy CoxGroup Finance ManagerCommented:
Try this

Option Explicit

Private Sub TextBox1_Change()
Const MaxLength As Integer = 20

Me.Label1.Caption = "Characters left: " & MaxLength - Len(Me.TextBox1.Text)
End Sub

Open in new window

texct-Length.xlsm
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
RWayneHAuthor Commented:
Both great, options..  as a related question, if the char limit is exceeded, I want to chg the Caption on the OK button, and as it stays within char limit keep it as is or = OK  Is this a change() for the OK button?
0
 
ShumsDistinguished Expert - 2017Commented:
Try Something like below:
Private Sub TextBox1_Change()
Dim RemText As Integer, TextCount As Integer
TextCount = 10
RemText = TextCount - TextBox1.TextLength
Label1.Caption = RemText
If TextBox1.TextLength > TextCount Then
    MsgBox "Text Exceeded", vbOKCancel
End If
End Sub

Open in new window

0
 
RWayneHAuthor Commented:
Thanks to all, great options to use.
0
 
Roy CoxGroup Finance ManagerCommented:
I think it is more professional to disable the commandbutton if the max is exceeeded.

Option Explicit

Private Sub TextBox1_Change()
Const MaxLength As Integer = 20

Me.Label1.Caption = "Characters left: " & MaxLength - Len(Me.TextBox1.Text)

Me.CommandButton1.Enabled = Len(Me.TextBox1.Text) - (MaxLength - 1)
End Sub

Open in new window

0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome! Glad we could help.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Roy

I took care of that in my solution. The length of text will not exceed the MaxCharLength set in the code.
0
 
Roy CoxGroup Finance ManagerCommented:
Subodh

I didn't see your post until after I posted, but my code disables the button if the character length is exceeded, which would prevent the button being clicked
0
 
Roy CoxGroup Finance ManagerCommented:
Typo in my last code, should be

Private Sub TextBox1_Change()
Const MaxLength As Integer = 20

Me.Label1.Caption = "Characters left: " & MaxLength - Len(Me.TextBox1.Text)

Me.CommandButton1.Enabled = Len(Me.TextBox1.Text) <= (MaxLength - 1)
End Sub

Open in new window

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

All Courses

From novice to tech pro — start learning today.