Display count of characters in a cell while typing.

[Toby Erkson]

Not sure if this should be here or in the worksheet.functions newsgroup but I
think here is a safe spot.

As a user types text into a cell I would like to have another cell display the
number of characters that are currently in it. The count has to be 'live',
meaning that as the person is typing the counter is updating, not simply
updating after the user has pressed 'Enter'.

Alternately, a count-down counter would be even nicer ;-)

Suggestions? Search brought up nothing.

 

[Frank Kabel]

Hi
not really possible as macros don't run while you're in editing mode

 

[Toby Erkson]

Darn. Thanks

 

[Earl Kiosterud]

Toby,

If you really need this, a macro could show a form, and you could type into
a text box, and the character count be put in the form (or anywhere else)
via macro code as you type. When done, the typed data could be put into the
cell that was selected when the whole thing started.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Darn. Thanks

 

[Toby Erkson]

Excellent suggestion! That's something I could probably do myself, thank you

 

[Earl Kiosterud]

Toby,

Go for it. Some ideas. You could have the form pop up when the user
selects a cell in the relevant column via Worksheet_SelectionChange event.
If it's put up modeless, (XL2000 and up), the user can click on some other
column, and you can have it go away automatically. If the user uses
backspace, you'll need to back the count down, or maybe better yet, count
them each time with something like
Len(TextBox1.Value)
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Excellent suggestion! That's something I could probably do myself, thank you

 

[Toby Erkson]

"Go for it"? ¿What?! I was hoping one of you MVP's would already have such a
program already on one of your web pages or would whip out the code and post
it, saving my lazy brain from more work ;-) LOL, just kidding! Thanks for
the ideas, I'll see if my counterpart wants to go this route.

--
Toby Erkson
Oregon, USA
Excel 2002 in Windows XP

Toby,

Go for it. Some ideas. You could have the form pop up when the user
selects a cell in the relevant column via Worksheet_SelectionChange event.
If it's put up modeless, (XL2000 and up), the user can click on some other
column, and you can have it go away automatically. If the user uses
backspace, you'll need to back the count down, or maybe better yet, count
them each time with something like
Len(TextBox1.Value)

....

 

[Earl Kiosterud]

Toby,

This'll get you started. Make UserForm1. Put TextBox1 in it, and a label
and name it lblCharCount, and a button and name it btnOK.

In the sheet module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then ' are we in column A
UserForm1.TextBox1.Value = ActiveCell.Value ' (note 1)
UserForm1.TextBox1.SetFocus
UserForm1.Show
End If
End Sub

In the code module for UserForm1 (double-click any item in it to open the
code module):

Private Sub btnOK_Click()
Selection = TextBox1.Value ' put data into cell
UserForm1.Hide
Selection.Offset(1, 0).Select ' move down
End Sub

Private Sub TextBox1_Change()
lblCharCount.Caption = Len(TextBox1.Value) ' put current character count
End Sub

Note 1: To prevent the text box from starting with the value of the cell
just entered, change this line to UserForm1.TextBox1.Value = ""

After entering the data into the text box, press Enter twice to save
reaching for the mouse and clicking OK. To get out of the whole deal, click
the X. You may want to put a Cancel button, and/or set it up to press Esc
to kill the user form. You can change this to a modeless userform (Excel
2000 and up) and the user could simply click in another cell outside column
A and the code could make the form go away.