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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsExcel & VBA ExpertCommented:
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
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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

ShumsExcel & VBA ExpertCommented:
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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.