Textbox form to Textbox in worksheet

[Henk]

I want the text typed in by a user in a textbox on a userform to be
transferred to a textbox in the worksheet.

Capturing the typed text is easy :

Textbox.Value

Creating a textbox on the sheegt a bit more complex :

ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1", Link:=False,
DisplayAsIcon:=False, Left:=528.75, Top:=204, Width:=432,
Height:=77.25).Select

But how do I get the Textbox.Value into the new created textbox?

 

[Jacob Skaria]

One way....

Dim obj As Object
Set obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1",
Link:=False, DisplayAsIcon:=False, Left:=528.75, Top:=204, Width:=432, _
Height:=77.25)

obj.Object.Text = "something"

If this post helps click Yes

 

[Henk]

Great! Working.

But now the second step. How do I capture the text from the textbox in the
worksheet in a string variable to transfer it somewhere else?

 

[Jacob Skaria]

'if you know the name..
Activesheet.oleobjects("TextBox1").object.text

OR
'if you have only one text box in the activesheet
Activesheet.oleobjects(1).object.text


If this post helps click Yes

 

[Henk]

tHenks Jacob, your help is highly appreciated.

This all is part of a complex code creatring a sort of batch sending charts
from Excel to a PowerPoint presentation. That is all working fine now, but I
want to give the user the possibility to add comments to the charts.
Capturing the comment is okay now, but I have to transfer it to PowerPoint
once it is on the Excel sheet and the batch procedure is started. I tried to
do something like this :

If Comment = True Then

PPSlide.Shapes.AddTextbox(Type:=msoTextOrientationHorizontal,
Left:=100, Top:=100, Width:=200, Height:=50).TextFrame.TextRange.Text =
TheCommentBelongingToTheChart

End If

The code is nearly copied from Help in PowerPoint, but I get the message :

Named argument not found.

I think it has something to do with :

Type:=msoTextOrientationHorizontal

Any idea?