Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying text from regular textbox
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying text from regular textbox
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying text from regular textbox
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying text from regular textbox
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying text from regular textbox
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying text from regular textbox
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying text from regular textbox
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(msoTextOrientationHo rizontal, 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 ------------------------------------------------------------------------ Greiffenberg's Profile: http://www.excelforum.com/member.php...o&userid=25311 View this thread: http://www.excelforum.com/showthread...hreadid=388003 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying text from regular textbox
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 -- Greiffenber ----------------------------------------------------------------------- Greiffenberg's Profile: http://www.excelforum.com/member.php...fo&userid=2531 View this thread: http://www.excelforum.com/showthread.php?threadid=38800 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying text from regular textbox
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying text from regular textbox
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 Greiffenberg wrote: 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 exits the sheet the text from textbox is copied to a cell, or rather to a range of cells, because i would like the "copy to" cell shifts down each time a cariage return appears. In other words: I would like to make a macro that behaves exactly as when I select the box text, copy it, select a cell and paste it... Can anyone help/start me up? Greiffenberg |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying text from regular textbox
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How copy a cell with mixed text (regular, bold, italic) with a fo. | Excel Discussion (Misc queries) | |||
ALL CAPS to regular text? | Excel Worksheet Functions | |||
textbox value copying | Excel Programming | |||
Copying Text from TextBox into cells in table and then addign a new row | Excel Programming | |||
Copying TEXT from Textbox to other location | Excel Programming |