Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy cell contents to a textbox 255 characters
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy cell contents to a textbox 255 characters
You might be able to use this method:
http://support.microsoft.com/default...;en-us;q213802 XL2000: How to Copy Text to Text Boxes Using the Characters Method http://support.microsoft.com/default...;en-us;q148815 XL: How to Copy Text to TextBoxes Using the Characters Method -- Regards, Tom Ogilvy "mcadle " wrote in message ... 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 macro 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 than 255 characters in it. It is a synopsis of proposals. It sometimes has up to 5000 characters. The query, run by the macro, retrieves all of the data regardless of string length. I need a way to copy or link this cell to another sheet where the text will be autoformatted. I currently have a textbox where I made the end user manually copy the text but now the demand is for it to be automated. Does anybody have any ideas at all? I have done a straight control source link on the textbox to the appropriate linked cell, but it only displays 255 char. I have tried a macroed copy paste but if more than 255 char it causes an error. I am at a loss now. Hopefully, somebody will have an idea or figure out what I am doing wrong. As always, thank you for your time in advance. Mark --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy cell contents to a textbox 255 characters
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? --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy cell contents to a textbox 255 characters
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. -- Regards, Tom Ogilvy "mcadle " wrote in message ... 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? --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy cell contents to a textbox 255 characters
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 -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy cell contents to a textbox 255 characters
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 -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy cell contents to a textbox 255 characters
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 a 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. --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy cell contents to a textbox 255 characters
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i display cell contents greater than 255 characters in a t. | Excel Discussion (Misc queries) | |||
how do I display cell contents when I am over 32, 767 characters? | Charts and Charting in Excel | |||
Copy textbox contents | Excel Programming | |||
Copy textbox contents to a range on a sheet | Excel Programming | |||
UserForm TextBox to ActiveSheet TextBox over 256 characters | Excel Programming |