Hi - need help
I would like to pass text from one text box to another. The text is more
than 255 characters in length which Excel has a problem with.

I have tried this:
Sub LoadBox(FBox, TBox)
Set F1 = ActiveSheet.Shapes(FBox)
Set T1 = ActiveSheet.Shapes(TBox)
P = F1.TextFrame.Characters.Text
With T1
.TextFrame.Characters.Text = ""
For i = 0 To Int(Len(F1.TextFrame.Characters.Text) / 255)
.TextFrame.Characters(.TextFrame.Characters.Count + 1).Insert _
Mid(P, (i * 255) + 1, 255)
Next
End With
End Sub

- but this will only pass 255 characters.
Any help appreciated.
TIA
Andrew Bourke

http://support.microsoft.com/kb/q148815/
How to Copy Text to TextBoxes Using the Characters Method

And since you're working with textboxes, I'd just declare those things as
textboxes...

Option Explicit
Sub testme()
Call LoadBox("fbox", "tbox")
End Sub
Sub LoadBox(fbox As String, tbox As String)
Dim F1 As TextBox
Dim T1 As TextBox
Dim i As Long

Set F1 = ActiveSheet.TextBoxes(fbox)
Set T1 = ActiveSheet.TextBoxes(tbox)

T1.Text = ""

For i = 1 To F1.Characters.Count Step 250
T1.Characters(i).Insert _
String:=F1.Characters(Start:=i, Length:=250).Text
Next i
End Sub

(I think it makes it easier than using the shapes collection.)


Andrew B wrote:

Hi - need help
I would like to pass text from one text box to another. The text is more
than 255 characters in length which Excel has a problem with.

I have tried this:
Sub LoadBox(FBox, TBox)
Set F1 = ActiveSheet.Shapes(FBox)
Set T1 = ActiveSheet.Shapes(TBox)
P = F1.TextFrame.Characters.Text
With T1
.TextFrame.Characters.Text = ""
For i = 0 To Int(Len(F1.TextFrame.Characters.Text) / 255)
.TextFrame.Characters(.TextFrame.Characters.Count + 1).Insert _
Mid(P, (i * 255) + 1, 255)
Next
End With
End Sub

- but this will only pass 255 characters.
Any help appreciated.
TIA
Andrew Bourke

--

Dave Peterson

Thanks Dave, it works beautifully. I didn't realise that you could refer
to a text box from the Drawing Toolbar as a text box, I assumed it would
have to be referred to as a shape.

Regards
Andrew

Dave Peterson wrote:
http://support.microsoft.com/kb/q148815/
How to Copy Text to TextBoxes Using the Characters Method

And since you're working with textboxes, I'd just declare those things as
textboxes...

Option Explicit
Sub testme()
Call LoadBox("fbox", "tbox")
End Sub
Sub LoadBox(fbox As String, tbox As String)
Dim F1 As TextBox
Dim T1 As TextBox
Dim i As Long

Set F1 = ActiveSheet.TextBoxes(fbox)
Set T1 = ActiveSheet.TextBoxes(tbox)

T1.Text = ""

For i = 1 To F1.Characters.Count Step 250
T1.Characters(i).Insert _
String:=F1.Characters(Start:=i, Length:=250).Text
Next i
End Sub

(I think it makes it easier than using the shapes collection.)

Those were the controls that were used exclusively in pre-xl97 versions of excel
(and still Mac versions????).

When xl97 appeared, so did those controls from control toolbox toolbar and the
controls from the Forms toolbar took a back seat (and are actually hidden in the
object browser--but visible if you toggle an option).

But MS didn't want to break all those excel workbooks that used those older
controls. So they're still supported in their "original" state (as opposed to
just a generic shape).



Andrew B wrote:

Thanks Dave, it works beautifully. I didn't realise that you could refer
to a text box from the Drawing Toolbar as a text box, I assumed it would
have to be referred to as a shape.

Regards
Andrew

Dave Peterson wrote:
http://support.microsoft.com/kb/q148815/
How to Copy Text to TextBoxes Using the Characters Method

And since you're working with textboxes, I'd just declare those things as
textboxes...

Option Explicit
Sub testme()
Call LoadBox("fbox", "tbox")
End Sub
Sub LoadBox(fbox As String, tbox As String)
Dim F1 As TextBox
Dim T1 As TextBox
Dim i As Long

Set F1 = ActiveSheet.TextBoxes(fbox)
Set T1 = ActiveSheet.TextBoxes(tbox)

T1.Text = ""

For i = 1 To F1.Characters.Count Step 250
T1.Characters(i).Insert _
String:=F1.Characters(Start:=i, Length:=250).Text
Next i
End Sub

(I think it makes it easier than using the shapes collection.)


--

Dave Peterson