Copy text from a cell to a textbox more than 255 characters

[mcadle]

All,

I have an interesting problem. I am retrieving external data from a SQL server. In a cell (A2 we'll say) there is text of more than 3000 characters. It is a synopsis of the report data. I need to copy all of the text in cell A2 to a textbox that resides on another sheet. I have tried the solutions at support.microsoft.com and it works (from a cell to a textbox) but not for more than 255 characters. Does anybody have any ideas? I am at desperate times. Thanks in advance for your help.

 

[parry]

Re: Copy text from a cell to a textbox more than 255 charact

Hi, yes you can have 3000 words in a TextBox, but I pity the people who have to read that much text within a control.

I have tested this (see code below) on XL2002 with a TextBox from the Visual Basic toolbar. Make sure you have scrollbars and that wordwrap and multiline are true in the box properties.

Sub test()
Sheet1.TextBox1 = Application.WorksheetFunction.Rept("A", 3000)
MsgBox Len(Sheet1.TextBox1.Value)
End Sub

 

[mcadle]

Re: Copy text from a cell to a textbox more than 255 charact

Sorry I don't think I understand. Let me clarify a bit. I want to make this macroed -- in a sense. Basically, I have a command button on the main sheet that on click refreshes the information from the database (the last record updated.) On the sheet where the data is retrieved, there is the cell A2 which contains the synopsis (3000+ characters) I need this cell's contents, whatever the length maybe, to be copied (when the button is clicked) to a different sheet that has a textbox.

The reason it is this way is, another company is emailed this excel file and they have their own macros to put the data into their database. Greatfully, nobody has to read all of the text in the control. I have requested access to just export our data to their db, but they won't do it. So, I am stuck doing it this way.

 

[parry]

Re: Copy text from a cell to a textbox more than 255 charact

The code I posted was just to show that you can place more than 255 characters where you were saying you couldnt get it to work. If they have a cell value or a TextBox they will still need to transfer the data to their database. Seems a waste of time just putting it in a textbox but I guess if they are happy then why worry.

The example below shows how to copy a cell value to a TextBox.

Sub Copy2TextBox()
'Copy contents of cell A2 on Sheet1 to TextBox1 on Sheet3. This will replace the existing
'contents of the TextBox
Sheets("Sheet3").TextBox1.Text = Sheets("Sheet1").Range("A2").Value
End Sub

 

[mcadle]

Re: Copy text from a cell to a textbox more than 255 charact

Hmm... Thanks for the help and sorry for the misunderstanding. When I put the code in the macro I get this error:

Run-Time Error '438':

Object doesn't support this type or method

When I click debug it takes me to the line of code.

Sheets("CONT1").Continuation1.Text = Sheets("Sheet2").Range("AT2").Value

The textbox is named "Continuation1" Is this a syntax issue or an operator error? Thanks again for your help. Anything is better than nothing.

 

[parry]

Re: Copy text from a cell to a textbox more than 255 charact

Hi again, there are TextBoxes and TextBoxes. Im referring to a TextBox control created from the Visual Basic toolbar - are you meaning from the Drawing toolbar? Excel sees these as different things.

Run this macro below which will find the name of each object on your sheet and also provide a Type number. The number represents a type of control with a 12= Visual Basic toolbar TextBox and 17=Drawing toolbar TextBox.

Sub FindControlDetails()
Dim Obj

For Each Obj In Sheets("CONT1").Shapes
MsgBox "Name: " & Obj.Name & vbLf & "Object Type #: " & Obj.Type
Next Obj
End Sub

If your control is a 17 then this code should do it. Check that the name is correct as well when you run the FindControlDetails macro.

Sub UpdateBox()
Sheets("CONT1").Shapes("Continuation1").Select
Selection.Characters.Text = Sheets("Sheet2").Range("AT2").Value
End Sub

EDIT: Backtracking to your original problem, I think a TextBox from the drawing toolbar has this 255 character limit, so you cannot get around this. Use a TextBox from the VBA toolbar instead.

 

[mcadle]

Re: Copy text from a cell to a textbox more than 255 charact

Thanks alot you have helped me soooo much. I just changed the textbox to a VB textbox (big difference and I never even thought about them) and set the linked cell property to the Sheet2!AT2 and the mutline property to TRUE and everything worked beautifully. Thanks alot, I can not express my gratitude enough for your help.

 

[parry]

Re: Copy text from a cell to a textbox more than 255 charact

My pleasure. Its a great resource this site so continue to come here and ask your questions or help others if you know the answer.

regards
Parry.