Please Help: Merged Cell Question

[Dino1]

How to limit alphanumeric data input AND/OR line spacing in a merged cell area. Data Validation or VB?
Have read Data Validation posts, good stuff. But have deeper question.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Situation: Have worksheet with merged cell area E1 to AE7 (27 columns, 7 rows) for user alphanumeric data input. Have Data Validation set to, Allow: Text length, Data: less than or equal to, Maxium: 650 and Error Message set to pop up when 650 exceeded. Also, merged cell area set with Format Cells menu, Number tab Catagory set to General for alphanumeric input.<o></o>
<o></o>
Example Problem 1: Error Message doesn't pop up on 651st character entered. Pops up when user moves to next cell area after the fact, 1000 character. Then user has to go back and edit/shorten data input not knowing exactely where 650th character is at. Very frustrating for user.<o></o>
<o></o>
Example Problem 2: Same merged cell area. If user inputs some data, then uses key combination Alt/Return to line space down a couple of lines (new sentance), and enters more data less than 650. The Data goes further down than merged cell area set (7 rows), behind next cell area. When worksheet printed, data entered past cell area (7 rows) is cut off, missing.<o></o>
<o></o>
Example Problem 3: If merged cell area is expanded to 10 rows and/or 1000 characters. After data entered and move to next cell area, data entered changes to many ####### characters. Excel help file states columns/cells are not wide enough for data or wrong Catagory chosen in Format Cells menu. Neither are correct. I have 27 merged cells and have increase it, didn't help, and have changed the Format Cells menu Catagory to several different things, didn't help. Only decreasing to 7 rows and 650 characters worked after many combination tries.<o></o>
<o></o>
Question: I would like to increase merged cell area to 15 rows and 1500 characters. Can anyone show/explain to me how to limit alphanumeric data input AND/OR line spacing (Alt/Enter). And an Error Message pops up right after when either of those situations are exceeded (15 rows and 1500 characters), not after the fact. And no ######'s thank you.<o></o>
<o></o>
Maybe a formula to use in Data Validation or Visual Basic code, or both. I know where VB is at on my menu, but I don't know how to use VB. So if you can explain step by step what to click and select if you sugest using a VB code.<o></o>
<o></o>
I greatly appreciate any help or suggestions from anyone on these problems. I have read alot in Excel help, and have read alot of posts over the past few weeks before posting these questions, and don't know what else to do.<o></o>
<o></o>
Thank you all in an advance, for anyone who can help.

 

[shg]

Data validation occurs after the user attempts to enter data into a cell, not during. There is no way for VBA or any other inbuilt capability (that I know of) to stop data entry at a fixed character length.

 

[Dino1]

Thank you SHG for the reply. That makes sense.

Do you or anyone else have an answer about the ##### signs or being able to increase the merged cell area to 15 rows, 1500 characters without problems?

Thank you in advance.

 

[VoG]

Maybe look here http://www.mrexcel.com/forum/showthread.php?t=546988

 

[Smitty]

Welcome to the Board!

Not to rain on your parade, but Excel isn't well suited as a text editor. You're better off using something like Word if you'll be forcing people to do it frequently.

Unfortunately, once you're in edit mode in a cell, no other action can take place until you exit edit mode, which as shg suggests, rules out validating string length as it's being input.

Also note that Merged cells suck, and can cause more headaches then they're worth.

You might consider a user form and allow the user to input whatever length they desire, then have a submit button that can evaluate the string length, parse it out in 650 character chunks, and paste that into the sheet.

HTH,

 

[Dino1]

Thank you VoG and Smitty.

I will try those examples. I still wonder about limiting the line spacing when word wrap is on or when a user uses "Alt/Enter" to line space down in merged cell area then continues entering more data.

Thanks again.