Thanks
Jack
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.
--
Gary''s Student - gsnu200716
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.
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.
--
Dave Peterson
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
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
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
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.
--
Dave Peterson
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
--
Dave Peterson
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
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.
--
Dave Peterson