Getting around the 255 char limitation in linked sheets?

[ElEdwards]

I'm using two workbooks, one in our newsroom for entry of data (input.xls) and one in our control room for output (output.xls)

output.xls has a link to input input.xls, so that if someone enters data into, say, cell A10 in input.xls, that entry is reflected in cell A10 of output.xls..... simple and it works, unless more than 255 characters is entered in the input workbook.

The M$KB has an article (Q211878) about this and a suggested workaround which (of course) doesn't work.

Do any of you have a method work getting around this? Thanks!

 

[Damon Ostrander]

Hi EIE,

Here is a workaround that might be satisfactory to accomplish what you want. What this does is allow you to enter text in a textbox control--rather than directly in the cell--and when the number of characters in the textbox exceeds 255 characters it automatically continues entering the text in the next cell down (in your case A11). It continues spilling down each time the 255 character limit is reached. To link all the text to output.xls just link A10 to A10, A11 to A11, A12 to A12, etc., in the two workbooks.

Here's how to do this:

1. Put a Controls (ActiveX) textbox on the input worksheet using the Control Toolbox. Set its MultiLine property to True using its Properties window.

2. Double click on the control while it is still in the Design mode and place the following code into its event code pane:

Private Sub TextBox1_Change()
Dim TxtLen As Integer
Dim ChStart As Integer
Dim RowOffset As Integer
TxtLen = Len(TextBox1.Text)
ChStart = 1
RowOffset = 0
Do
[A10].Offset(RowOffset) = Mid(TextBox1.Text, ChStart, 255)
ChStart = ChStart + 255
RowOffset = RowOffset + 1
Loop Until ChStart > TxtLen
End Sub

3. Go back to Excel and exit Design mode. Type more than 255 characters of text into the textbox and see what happens.

I didn't bother to make the code efficient or to handle the breaking of words across cells, but this could be done with a bit of extra code. If this solution works for you and you want these improvements, let me know.

 

[ElEdwards]

Damon, I'm sure your solution works.. but not for my application.

These two files (input.xls & output.xls) are used in a TV station to feed a Chyron character generator serially. Have you seen the crawls at the bottom of the screen on CNN, MSBNC, FOX, etc.? That's what we are doing.
There is another program in the loop that reads the output.xls file, parses it and feeds that info to the character generator.

Your solution won't work in this instance because between every bit of cell data, our logo is inserted, which serves as a separator for statements.

I'm trying to solve the problem of being limited to 255 characters for each statement. Granted, we don't usually have that many characters in a statement (most folks can't read that much on a continuous crawl), we ran into the limitation when we created a sheet that had every name of the dead/missing from the WTC attack. We would have rather had just the names without or logo between them but the limitation prevented that.

Sorry to be so long winded but I'm trying to explain the application of these files.

Thanks!

 

[klb]

Have you tried using Data Validation to limit the number of characters in the cells in your input.xls?

 

[neauxway]

This almost solves what I was needed to do but need to not break in the middle of a word. I had to change it a little to get it to erase all text when backspacing, was leaving the first character on every line but first. This is what I have:

Private Sub TextBox1_Change()
Dim TxtLen As Integer
Dim ChStart As Integer
Dim ChEnd As Integer
Dim ChCount As Integer
Dim RowOffset As Integer
TxtLen = Len(TextBox1.Text)
ChStart = -100
ChCount = 0
ChEnd = 101
RowOffset = -1
Do
ChStart = ChStart + ChEnd
RowOffset = RowOffset + 1
[A26].Offset(RowOffset) = Mid(TextBox1.Text, ChStart, ChEnd)
Loop Until ChStart > TxtLen
End Sub

Some varibles were added while I was trying to get it to break on full word. Any help would be appriciatied. Thanks