copy cell contents to a textbox > 255 characters

[mcadle]

Hello all,

I have been searching for a solution for a long time but to no avail.
I have a complex workbook with multiple sheets. When a startup macr
is run it connects to my sql server and retrieves the relevant data.
My problem is this: One of the fields that is retrieved has more tha
255 characters in it. It is a synopsis of proposals. It sometimes ha
up to 5000 characters. The query, run by the macro, retrieves all of th
data regardless of string length. I need a way to copy or link thi
cell to another sheet where the text will be autoformatted.
currently have a textbox where I made the end user manually copy th
text but now the demand is for it to be automated. Does anybody hav
any ideas at all?

I have done a straight control source link on the textbox to th
appropriate linked cell, but it only displays 255 char. I have tried
macroed copy paste but if more than 255 char it causes an error. I a
at a loss now. Hopefully, somebody will have an idea or figure ou
what I am doing wrong. As always, thank you for your time in advance.

Mar

 

[Tom Ogilvy]

You might be able to use this method:

http://support.microsoft.com/default.aspx?scid=kb;en-us;q213802
XL2000: How to Copy Text to Text Boxes Using the Characters Method

http://support.microsoft.com/default.aspx?scid=kb;en-us;q148815
XL: How to Copy Text to TextBoxes Using the Characters Method

 

[mcadle]

The only problem is that it will only copy strings of 255 characters. I
was wondering if there was a way to modify this and make it loop and
copy all characters in strings of 255 char until the end. Any ideas?

 

[Tom Ogilvy]

guess you didn't look at the code (example 1) in the article. It copies
strings well in excess of 255 characters - it just does them 250 characters
at a time, looping to get all the characters.

 

[mcadle]

Ok sorry about that. I only looked at the description from textbox t
textbox. Can I set the first textbox to be a cell? If so how would
go about doing that?

I don't know how but when I query the database, excel puts easily up t
5000 char. into a cell. I personally thought a cell had limits of 25
char. and wouldn't accept anything larger. Thank you for your rapi
responses

 

[mcadle]

Ok man I really need to read the articles that you posted. Sorry, whe
I first started this quest I found these articles. I didn't read th
code I only read the description.

I would assume with example 2 instead of a range I could specify
specific cell or the cell I need and a blank cell next to it right?
Thanks again for pointing out the obvious

 

[mcadle]

OK I have been trying several things. First off the code works only for
active sheets (probably a minor issue but I will need it to move the
info to a non active sheet.) I have succesfully made both examples
work. The problems are:

1. Example 2 copy text from a range of cells has a limitation of 255
chars.

2. Example 1 works for more than 255 chars. but my data resides in a
cell to start off with and not a textbox.

SO...I have tried a combination of things. I figured that if I copied
all contents from a cell to a textbox then I would alter the Example 1
to copy the contents of one textbox to another that resides on a
different sheet.

Here is the code I have tried so far.

'declare the variables
Dim txtBox1 As TextBox
Dim theRange As Range, cell As Range
Dim x As Integer
Dim theText As String
'define variables
Set txtBox1 = ActiveSheet.DrawingObjects("test")
Set theRange = ActiveSheet.Range("AT3:AT4")
'loop and copy the stuff
For Each cell In theRange
For x = 1 To txtBox1.Characters.Count Step 250
theText = cell.Characters(Start:=x, Length:=250).Text
txtBox1.Characters(Start:=x, Length:=250).Text = theText
Next
Next cell
'cuss a lot because it doesn't work

OK obviously this doesn't work. I am looking for any help I can get.
Also, how do you reference a hidden sheet in a macro. I.e. I want the
sheet where all of the data retrieved from the query to be hidden. When
I hide the sheet the macro on run causes an error. Thanks again for the
help.

 

[mcadle]

bum