Microsoft Excel - Is there a way to not allow characters to be added once the specified cell limit is met

Asked By Sammi Jo Pumphrey on 13-Sep-12 09:53 AM
I have a form that I created in excel that is used by 20 different offices.  There is a field that they must enter a description into.  I have set the data validation to only allow 50 characters, however, instead of getting an error message once the entire description is entered is there a way once the 50 character limit is reached they cannot type anything else into that cell? 
Robbe Morris replied to Sammi Jo Pumphrey on 13-Sep-12 10:01 AM
See if you have access to a OnKeyUp, OnMouseUp, and OnBlur event(s). Not sure if you are using a TextBox or just a simple cell.  The tactic is to react to every change and call the same Sub/Function to check the length.  If it exceeds your limit, you perform a substring on the value up to your maximum length.
Sammi Jo Pumphrey replied to Robbe Morris on 13-Sep-12 10:15 AM
Thanks for your reply, unfortunately, you are going to have to "dummy" it up for me.  I am using a simple cell.  I would like to avoid using macros and code if possible.  
Robbe Morris replied to Sammi Jo Pumphrey on 13-Sep-12 10:37 AM
I don't know that Excel supports this with macros/code.
wally eye replied to Sammi Jo Pumphrey on 13-Sep-12 12:13 PM
It would seem you are doing this in the Excel GUI, there is no way to disallow more characters being added, through the GUI.  You can, using data validation limit them to a set number of characters, using a Custom setting:

=LEN(A2)<51

If you want to do it mid-stream, you will have to use some VBA, and it won't be easy as worksheets don't have a keydown/keypress event.  I've been looking for an hour and still haven't found anything that works, getting pretty deep.  The closest I've found is here:

http://www.mrexcel.com/forum/excel-questions/181654-keypress-event-worksheet-cells.html

If you are wanting to avoid VBA, I think this will be a bit too deep for you, it certainly is for me!

I think you are out of luck for limiting characters on the fly.
John D replied to Sammi Jo Pumphrey on 13-Sep-12 04:20 PM
HI
You can do it simply by using a Textbox from the control Toolbox  instead of your Data Validation box.Once you created the textbox, right click on your mouse and select "Properties" then look for "MaxLength" and type in 50. That it.
John