Copying text from regular textbox

[Greiffenberg]

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

 

[Gareth]

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]

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

 

[Gareth]

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

 

[Anne Troy]

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

www.OfficeArticles.com

 

[DM Unseen]

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

 

[Greiffenberg]

Thank you all for the help.

I don't know what is wrong with this version of XL, but DM unseens tip
will not work either!?!

This is how a textbox is made (recorded macro):

Sub add_textbox()
'
' add_textbox Macro
' Macro recorded 19/07/2005 by Ask Greiffenberg
'

'
Application.CommandBars("Drawing").Visible = True
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 226#,
84#, _
214#, 323#).Select
Selection.Characters.text = ""
With Selection.Font
..Name = "Verdana"
..FontStyle = "Regular"
..Size = 10
..Strikethrough = False
..Superscript = False
..Subscript = False
..OutlineFont = False
..Shadow = False
..Underline = xlUnderlineStyleNone
..ColorIndex = xlAutomatic
End With
End Sub

I know it's possible to make boxes another way (don't know how),
perhaps that would help?

Greiffenberg

 

[Greiffenberg]

OK, thought I'd better be more precise about what I want to do...

I want a textbox on a sheet. The user enters text on it. When he exit
the sheet the text from textbox is copied to a cell, or rather to
range of cells, because i would like the "copy to" cell shifts dow
each time a cariage return appears.

In other words: I would like to make a macro that behaves exactly a
when I select the box text, copy it, select a cell and paste it...

Can anyone help/start me up?

Greiffenber

 

[DM Unseen]

You want users to enter text in a textbox which you then paste into
excel on a line by line basis?

(Maybe) use a textbox (from the Controls Toolbox) and link this to a
cell(say A1)

Now enter in a module:

Public Function SplitLines(strVal As String) As String()

SplitLines = Split(strVal, Chr(13) & Chr(10))

End Function

and now select the range where your lines should go and enter in the
formula bar : =TRANSPOSE(splitlines(A1)) and close with
CTRL+SHIFT+ENTER(Array formula!)

The lines shoudl now be visible in the selected range.

Another option would be to use the textbox lostfocus event to trigger
some VBA that does essentially the same (this should also work with
your current textbox).

Dm Unseen

 

[Gareth]

When you say "exits the sheet", it makes me think what you really need
is a userform with a textbox in it. e.g.

- Click button (say)
- Up pops a userform with a long, multiline textbox on it
- When you close the userform, your cells are populated.

Is this what you're after?

G

 

[Gareth]

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