Textbox form to Textbox in worksheet

  • Thread starter Thread starter Henk
  • Start date Start date
H

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?
 
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
 
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?
 
'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
 
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?
 
Back
Top