Real-time character counter

[Jack_Feeman]

Excel 2003
I am trying to display a character counter in real time. Scenario:
An Excel form has a comment cell where the user types a comment.
(We all know that Excel can display 1024 characters in a cell and 32,767 in
the formula bar. When you print, Excel can only print a portion of those and
help doesn't give the exact mount).
I need to print the entire contents of a cell up to the limitaions above.
The Excel Printing group suggested that I use the =LEN(cellref) to count the
characters in the cell to give the user a warning of how many characters they
have entered. Assuming that Excel can print what it displays in the cell
(1024 characters), I amended that fomrula to =1024-LEN(cellref) which works
good but only after you leave the comment cell.
Is there a way to make the results of this counter cell real-time? (To give
a running result while the user is still in the comment cell.)
If it is not possible, I would like to add a character counter to the cell
or adjacent cell so users know how many characters they have left before the
print copy won't show them. Anyone know where or how I can get/vba a counter?

Thanks
Jack

 

[Gary''s Student]

All you need is a routine to capture keystrokes, append them to a string,
display the string, and finally display the count of characters in the
string. For an example of capturing the keystrokes in realtime see:

http://www.oaltd.co.uk/Excel/Default.htm

CheckKey.zip (20 Aug 1996, 8k, 14363 downloads)
This file demonstrates how to check for a key press during a lengthy looping
routine. For example, your routine could display the message "Press the space
bar to cancel this operation.". The routine could check if the space bar had
been pressed at the start of each loop. It uses Windows API calls to check
the message buffer. Both 16-bit and 32-bit versions of the routine are
included.

 

[Jack_Feeman]

Thanks for the really fast answer. I will try it out.
Jack

 

[Dave Peterson]

Macros won't run while the user is editing the cell.

You could create a userform with a textbox on it and count the current number of
characters as they type.

And actually excel can print and show more than those 1024 characters in a
cell. If you add alt-enters (to force a new line within the cell) every 80-100
characters, you can see and print lots more.

 

[Gary''s Student]

Two more points:

1. remove Steve's timing counter from the VBA
2. remember to test for some kind of excape character to know when to quit
the macro.

 

[Jack_Feeman]

Thanks again

Two more points:

1. remove Steve's timing counter from the VBA
2. remember to test for some kind of excape character to know when to quit
the macro.

 

[Jack_Feeman]

Thanks Dave,
I had heard of the Alt-Enter method which would require users to remember to
enter it every so often which is a lot harder than it looks. Besides then the
form pagination would be way off if the user got long winded in one of the
comment cells. I will keep that in mind though.

I tried using a text box from the toolbox in a cell and the Userform is
another way to approach it. I will try them both. Thanks again
Jack

 

[Jack_Feeman]

I have tried all the great suggestions and for reasons peculiar to this form
we am developing, this is what we came up with and what we still need to
accomplish.

What we have done:
OUr form includes four comment cells: A1, D1, G1, J1. Above each comment
cell is a title cell (which identifies the comment cell) and a results cell
(which displays the number of characters entered in the comment cell
(=1024-(refcell)). 1024 is the limit we know will print out (without the Alt
Enter workaround). This works great but is not realtime. It only updates when
the user exits the cell or deletes the cell's contents.

What we still need to do:
Can we add an auto_execute macro that scans for focus? When the current
focus is on one of those four cells, it shows the character count via a
continuous loop until the cell loses focus (user exits the cell).

If not, can I use a UserForm to do the same? When focus is on one of those
four cells bring up the UserForm and start counting the number of characters
in the cell. And when the cell looses focus, the UserForm is canceled.

We are almost there and we really do appreciate all of your help. Thanks
Jack

 

[Dave Peterson]

No macro is going to run while the user is editing the cell.

You may just want to put a formula in an adjacent cell that warns the user when
they exceed your limit:

=if(len(a1)<1024,"","Warning--may not print!")

Format it in big bold red letters.

The suggestion I had with the userform meant that you had to get the input from
the user, count the characters and then plop the text in the userform's textbox
back into the cell.

If you want to try...

Rightclick on the worksheet tab that should have this behavior. Select View
code and paste this into the code window.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
If .Cells.Count > 1 Then Exit Sub
If Intersect(Me.Range("a1:a10,b3,g9:h14"), .Cells) Is Nothing Then
Exit Sub
End If
End With
UserForm1.Show
End Sub

I wanted a1:a10, B3, and G9:h14 to hold comments. Change that to what you want.

Then create a userform with a textbox and two buttons.
Put this code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim myStr As String
myStr = Replace(Me.TextBox1.Value, vbCr, "")
'for it to be treated as text
ActiveCell.Value = "'" & myStr
Unload Me
End Sub
Private Sub TextBox1_Change()
Dim myMax As Long
Dim myMsg As String

myMax = 1024

myMsg = "Length: " & Len(Me.TextBox1.Value) _
& " Remaining: " & _
Application.Max(0, myMax - Len(Me.TextBox1.Value))

Me.Caption = myMsg
End Sub
Private Sub UserForm_Initialize()
With Me.TextBox1
.WordWrap = True
.MultiLine = True
.EnterKeyBehavior = True
.Value = ActiveCell.Value
End With
With Me.CommandButton1
.Caption = "Cancel"
.Cancel = True
End With
Me.CommandButton2.Caption = "Ok"
End Sub


Some references:

David McRitchie's intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

Debra Dalgleish's intro to userforms:
http://www.contextures.com/xlUserForm01.html

 

[Dave Peterson]

Change the textbox_change routine to this:

Private Sub TextBox1_Change()
Dim myMax As Long
Dim myMsg As String
Dim myStr As String

myStr = Replace(Me.TextBox1.Value, vbCr, "")

myMax = 1024
myMsg = "Length: " & Len(myStr) _
& " Remaining: " & Application.Max(0, myMax - Len(myStr))

Me.Caption = myMsg
End Sub

When you hit enter in the textbox, excel adds to characters (carriage return and
line feed, or vbcrlf).

This removes those extra carriage returns (vbCR) from the length checks.

 

[Jack_Feeman]

Great ideas! and Great Response time
Thanks again
Jack

 

[Dave Peterson]

Eh, one more change. I didn't put the message in me.caption when the userform
was started. (If you didn't like using the caption of the userform, you could
always add a label and put whatever message you wanted in there (change the font
to red when you're over the limit???)

Option Explicit
Dim myMax As Long
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim myStr As String
myStr = Replace(Me.TextBox1.Value, vbCr, "")
'for it to be treated as text
ActiveCell.Value = "'" & myStr
Unload Me
End Sub
Private Sub TextBox1_Change()
Dim myMsg As String
Dim myStr As String

myStr = Replace(Me.TextBox1.Value, vbCr, "")
myMsg = "Length: " & Len(myStr) _
& " Remaining: " & Application.Max(0, myMax - Len(myStr))

Me.Caption = myMsg
End Sub
Private Sub UserForm_Initialize()

Dim myStr As String
Dim myMsg As String

myMax = 1024

With Me.TextBox1
.WordWrap = True
.MultiLine = True
.EnterKeyBehavior = True
.Value = ActiveCell.Value
End With
With Me.CommandButton1
.Caption = "Cancel"
.Cancel = True
End With
Me.CommandButton2.Caption = "Ok"

myStr = Replace(Me.TextBox1.Value, vbCr, "")
myMsg = "Length: " & Len(myStr) _
& " Remaining: " & Application.Max(0, myMax - Len(myStr))

Me.Caption = myMsg
End Sub

Great ideas! and Great Response time
Thanks again
Jack

 

[Jack_Feeman]

Hi DAve thanks for the additional feedback.

Since Excel cannot count characters in a cell while the user is editing it
but can count characters in a text box located in an UserForm; can I overlay
a testbox from the toolbox over the cell in question and apply this same vba
to thetextbox and achieve the same result: Character count shows in real time
in an adjacent cell?

Otherwise need to know how to link a userform to activate when a certain
cell is selected by the user?

Thanks
Jack

 

[Dave Peterson]

You could use code like this behind the worksheet:

Option Explicit
Private Sub TextBox1_Change()
With Me.TextBox1
.TopLeftCell.Offset(0, 5).Value = Len(.Value)
End With
End Sub

There is some worksheet_selectionchange code in one of the earlier replies that
would show the userform.

Hi DAve thanks for the additional feedback.

Since Excel cannot count characters in a cell while the user is editing it
but can count characters in a text box located in an UserForm; can I overlay
a testbox from the toolbox over the cell in question and apply this same vba
to thetextbox and achieve the same result: Character count shows in real time
in an adjacent cell?

Otherwise need to know how to link a userform to activate when a certain
cell is selected by the user?

Thanks
Jack

 

[Jack_Feeman]

Thanks Dave,
I really appreciate your quick responses.
Jack