I'm trying in VBA to copy the text from a textbox.

When I try recording the macro of what I'm doing it uses the specific
text in the box, but since I want to copy the users text this will not
work:

ActiveSheet.Shapes("Text Box 3").Select
Selection.Characters.Text = "Text here"
With Selection.Characters(Start:=1, Length:=10).Font
..Name = "Verdana"
..FontStyle = "Regular"
..Size = 10
..Strikethrough = False
..Superscript = False
..Subscript = False
..OutlineFont = False
..Shadow = False
..Underline = xlUnderlineStyleNone
..ColorIndex = xlAutomatic
End With
Selection.Copy
Range("E21").Select
ActiveSheet.Paste

any help?

Greiffenberg


--
Greiffenberg
------------------------------------------------------------------------
Greiffenberg's Profile: http://www.excelforum.com/member.php...o&userid=25311
View this thread: http://www.excelforum.com/showthread...hreadid=388003

I'm not positive exactly what you mean by copying the user's text - or
what you're trying to achieve overall - hopefully this will put you in
the right direction. In general, copying is rarely necessary when trying
to place text into a cell you can write directly.

Two ways of getting text from a textbox:

(1) Link it, so whatever is typed in the Textbox automatically appears
in the cell.
- Activate the Control Toolbox toolbar
- Switch into Design Mode
- Click your shape and then Properties on the toolbar.
- Set the LinkedCell property to E21
- Toggle out of design mode.

Now whatever is types into the Textbox will appear in E21.

(2) Alternatively, since you wanted to do this in VBA.
With ActiveSheet
.Range("E21") = .OLEObjects("TextBox1").Object.Text
End With
(Change TextBox1 name as appropriate.)


HTH,
Gareth



Greiffenberg wrote:
I'm trying in VBA to copy the text from a textbox.

When I try recording the macro of what I'm doing it uses the specific
text in the box, but since I want to copy the users text this will not
work:

ActiveSheet.Shapes("Text Box 3").Select
Selection.Characters.Text = "Text here"
With Selection.Characters(Start:=1, Length:=10).Font
Name = "Verdana"
FontStyle = "Regular"
Size = 10
Strikethrough = False
Superscript = False
Subscript = False
OutlineFont = False
Shadow = False
Underline = xlUnderlineStyleNone
ColorIndex = xlAutomatic
End With
Selection.Copy
Range("E21").Select
ActiveSheet.Paste

any help?

Greiffenberg

Thank you very much for your help, BUT:

Your tip 1 will not work since it's supposed to work on a mac, and XL
for Mac does not support Active X (I didn't tell since I didn't know it
would matter).

Your tip 2 gives me this error:
'1004'
Unable to get the OLEObjects property of the worksheet class

Is there any other way?

Greiffenberg


--
Greiffenberg
------------------------------------------------------------------------
Greiffenberg's Profile: http://www.excelforum.com/member.php...o&userid=25311
View this thread: http://www.excelforum.com/showthread...hreadid=388003

A Mac?! Hmmmm... the only Mac I'm familiar with is a big mac. But I'm
surprised there isn't a property you can set to link it to a cell value
- otherwise it seems a bit pointless having textboxes.

I'm a bit confused as what sort of textbox we have here. Could you try
recording a macro of you inserting a textbox onto your worksheet and
posting the recorded code. That should hopefully give me a better idea.

G

Greiffenberg wrote:
Thank you very much for your help, BUT:

Your tip 1 will not work since it's supposed to work on a mac, and XL
for Mac does not support Active X (I didn't tell since I didn't know it
would matter).

Your tip 2 gives me this error:
'1004'
Unable to get the OLEObjects property of the worksheet class

Is there any other way?

Greiffenberg

ROFL, Gareth!! The poor Mac users. Both of them really ought to consider
Windows, don't you think??
*******************
~Anne Troy

www.OfficeArticles.com


"Gareth" wrote in message
...
A Mac?! Hmmmm... the only Mac I'm familiar with is a big mac. But I'm
surprised there isn't a property you can set to link it to a cell value
- otherwise it seems a bit pointless having textboxes.

I'm a bit confused as what sort of textbox we have here. Could you try
recording a macro of you inserting a textbox onto your worksheet and
posting the recorded code. That should hopefully give me a better idea.

G

Greiffenberg wrote:
Thank you very much for your help, BUT:

Your tip 1 will not work since it's supposed to work on a mac, and XL
for Mac does not support Active X (I didn't tell since I didn't know it
would matter).

Your tip 2 gives me this error:
'1004'
Unable to get the OLEObjects property of the worksheet class

Is there any other way?

Greiffenberg

This is almost Cut&Paste form another post i just made;)

Do not use selection more then nescesary!(I repeat: Macro recorder sins
should not be repeated!)

Dim strVal as string
for a Shapecontrol (i.e. a control from the Forms toolbar) use

strVal = ActiveSheet.Shapes("Text Box 3").ControlFormat.Value

to get the text.

DM Unseen

teehee. It's probably that new fangled two buttoned mouse that puts them
off. Bless 'em.

Anne Troy wrote:
ROFL, Gareth!! The poor Mac users. Both of them really ought to consider
Windows, don't you think??
*******************
~Anne Troy

www.OfficeArticles.com


"Gareth" wrote in message
...

A Mac?! Hmmmm... the only Mac I'm familiar with is a big mac. But I'm
surprised there isn't a property you can set to link it to a cell value
- otherwise it seems a bit pointless having textboxes.

I'm a bit confused as what sort of textbox we have here. Could you try
recording a macro of you inserting a textbox onto your worksheet and
posting the recorded code. That should hopefully give me a better idea.

G

Greiffenberg wrote:

Thank you very much for your help, BUT:

Your tip 1 will not work since it's supposed to work on a mac, and XL
for Mac does not support Active X (I didn't tell since I didn't know it
would matter).

Your tip 2 gives me this error:
'1004'
Unable to get the OLEObjects property of the worksheet class

Is there any other way?

Greiffenberg