Character Counter - with a twist

Pinball · May 16th, 2002, 10:15 PM

I currently use data validation to limit the number of text characters in cell B1 to 40 characters maximum. I placed the formula
=LEN(B1) in cell B2 so that the user can see how many characters they entered.

This works great, but I would like to go one step further. I would like the user to be able to see how many characters they input as they are typing them, instead of having to hit enter first. I cannot think of any VBA event that will accomplish this.

Any ideas? Or is this impossible to do in Excel? Thanks!

kkknie · May 17th, 2002, 01:23 PM

From what I can tell, it is impossible. I do know that you can do it with the keypress event in a textbox if you really want this functionallity.

Mudface · May 17th, 2002, 01:29 PM

Agree with kkknie, the keydown, keypress and keyup events only apply to userforms and some of their controls. There isn't a corresponding event for worksheets, unless there's some clever API function going.

Al Chara · May 17th, 2002, 01:37 PM

Pinball,

Why don't you insert a textbox into cell B1 and have the user enter into the textbox instead of the cell. Then use the following code:

Private Sub TextBox1_Change()
[b2] = Len(TextBox1)
If Len(TextBox1) = 41 Then TextBox1 = Left(TextBox1, 40)
End Sub

May 17th, 2002, 01:37 PM	#4
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	Pinball, Why don't you insert a textbox into cell B1 and have the user enter into the textbox instead of the cell. Then use the following code: Private Sub TextBox1_Change() [b2] = Len(TextBox1) If Len(TextBox1) = 41 Then TextBox1 = Left(TextBox1, 40) End Sub __________________ Kind regards, Al Chara

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 16th, 2002, 10:15 PM	#1
Pinball Board Regular Join Date: Apr 2002 Posts: 107	I currently use data validation to limit the number of text characters in cell B1 to 40 characters maximum. I placed the formula =LEN(B1) in cell B2 so that the user can see how many characters they entered. This works great, but I would like to go one step further. I would like the user to be able to see how many characters they input as they are typing them, instead of having to hit enter first. I cannot think of any VBA event that will accomplish this. Any ideas? Or is this impossible to do in Excel? Thanks!

May 17th, 2002, 01:23 PM	#2
kkknie Board Regular Join Date: Apr 2002 Location: Greenwood, SC Posts: 677	From what I can tell, it is impossible. I do know that you can do it with the keypress event in a textbox if you really want this functionallity.

May 17th, 2002, 01:29 PM	#3
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	Agree with kkknie, the keydown, keypress and keyup events only apply to userforms and some of their controls. There isn't a corresponding event for worksheets, unless there's some clever API function going.