Getting around the 255 char limitation in linked sheets?

ElEdwards

Board Regular
Joined
Aug 29, 2002
Messages
220
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!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

Board Regular
Joined
Aug 29, 2002
Messages
220
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

Well-known Member
Joined
Apr 3, 2002
Messages
821
Have you tried using Data Validation to limit the number of characters in the cells in your input.xls?
 

neauxway

New Member
Joined
Jan 30, 2006
Messages
2
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
 

Forum statistics

Threads
1,144,275
Messages
5,723,449
Members
422,497
Latest member
dougy99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top