Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Real-time character counter
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Real-time character counter
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. -- Gary''s Student - gsnu200716 "Jack_Feeman" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Real-time character counter
Thanks for the really fast answer. I will try it out.
Jack "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200716 "Jack_Feeman" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Real-time character counter
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. Jack_Feeman wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Real-time character counter
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. -- Gary''s Student - gsnu200716 "Jack_Feeman" wrote: Thanks for the really fast answer. I will try it out. Jack "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200716 "Jack_Feeman" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Real-time character counter
Thanks again
"Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200716 "Jack_Feeman" wrote: Thanks for the really fast answer. I will try it out. Jack "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200716 "Jack_Feeman" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Real-time character counter
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 "Dave Peterson" wrote: 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. Jack_Feeman wrote: 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Real-time character counter
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 "Jack_Feeman" wrote: 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 "Dave Peterson" wrote: 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. Jack_Feeman wrote: 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 -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Real-time character counter
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 Jack_Feeman wrote: 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 "Jack_Feeman" wrote: 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 "Dave Peterson" wrote: 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. Jack_Feeman wrote: 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 -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Real-time character counter
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. Dave Peterson wrote: 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 Jack_Feeman wrote: 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 "Jack_Feeman" wrote: 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 "Dave Peterson" wrote: 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. Jack_Feeman wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Real-time character counter
Great ideas! and Great Response time
Thanks again Jack "Dave Peterson" wrote: 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 Jack_Feeman wrote: 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 "Jack_Feeman" wrote: 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 "Dave Peterson" wrote: 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. Jack_Feeman wrote: 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 -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Real-time character counter
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 Jack_Feeman wrote: Great ideas! and Great Response time Thanks again Jack "Dave Peterson" wrote: 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 Jack_Feeman wrote: 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 "Jack_Feeman" wrote: 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 "Dave Peterson" wrote: 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. Jack_Feeman wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Real-time character counter
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" wrote: 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 Jack_Feeman wrote: Great ideas! and Great Response time Thanks again Jack "Dave Peterson" wrote: 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 Jack_Feeman wrote: 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 "Jack_Feeman" wrote: 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 "Dave Peterson" wrote: 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. Jack_Feeman wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Real-time character counter
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. Jack_Feeman wrote: 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" wrote: 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 Jack_Feeman wrote: Great ideas! and Great Response time Thanks again Jack "Dave Peterson" wrote: 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 Jack_Feeman wrote: 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 "Jack_Feeman" wrote: 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 "Dave Peterson" wrote: 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. Jack_Feeman wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Real-time character counter
Thanks Dave,
I really appreciate your quick responses. Jack "Dave Peterson" wrote: 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. Jack_Feeman wrote: 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" wrote: 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 Jack_Feeman wrote: Great ideas! and Great Response time Thanks again Jack "Dave Peterson" wrote: 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 Jack_Feeman wrote: 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 "Jack_Feeman" wrote: 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 "Dave Peterson" wrote: 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. Jack_Feeman wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Local Apparent Time (LAT) - real / actual solar time ? | Excel Worksheet Functions | |||
dynamic character counter for merged cell text field | Excel Worksheet Functions | |||
How to set the time counter? | Excel Worksheet Functions | |||
Do While Loop using time as the counter | Excel Programming | |||
DDE real time and time dispay conflict | Excel Programming |