Automate Copy and Paste?
in [Programming]
|
From: Quin on 10 Feb 2010 15:11 I am using Microsoft Office Excel 2003 at work and Microsoft Office Excel 2007 at home. Using either one of these programs I need to transfer more (a lot more) than 1000 short lines of text from the cells into text boxes. Each line of text is in cells A1 through A1000. All the text boxes are adjacent to the cells. To do this with manual cut and paste will take forever. Just wondering if there is some simple code that would allow me to automate doing a “copy” with one mouse click from the cell containing the text ,and paste into the text box with a second click. (Every other click of the mouse would be a copy/paste) Any thoughts on this would be appreciated. Quin
From: ker_01 on 10 Feb 2010 15:52 Quin- Consider something like the following (watch for linewrap). Change the 3 fixed values to match your worksheet layout. This will really only work well if all your rows are the same height, but you could add some code to check row height (and adjust accordingly) if you wanted. I suggest leaving the number of rows to process at 5 until you adjust the other 3 values to your liking. HTH, Keith Sub MakeTextBoxes() NumberOfRowsToProcess= 5 FixedRowHeight = 10 FixedLeftSide = 80 FixedWidth = 50 For MyRow = 1 To 2 ActiveSheet.Range("A" & MyRow).Select tText = Sheet1.Range("A" & MyRow).Value 'ActiveSheet.Shapes.AddTextbox(orientation, left, top, width, fontsize) ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, FixedLeftSide, (FixedRowHeight * MyRow), _ FixedWidth, 10#).Select Selection.Characters.Text = tText Next End Sub "Quin" wrote: > I am using Microsoft Office Excel 2003 at work and Microsoft Office Excel > 2007 at home. Using either one of these programs I need to transfer more (a > lot more) than 1000 short lines of text from the cells into text boxes. Each > line of text is in cells A1 through A1000. All the text boxes are adjacent > to the cells. > To do this with manual cut and paste will take forever. Just wondering if > there is some simple code that would allow me to automate doing a “copy” with > one mouse click from the cell containing the text ,and paste into the text > box with a second click. (Every other click of the mouse would be a > copy/paste) > Any thoughts on this would be appreciated. > > Quin >
From: Quin on 10 Feb 2010 17:08 I get a "Compile Error" when I try to run the code in Excel 2007. Quin
From: ker_01 on 10 Feb 2010 17:36 This code will add a textbox to 2007 (you'll have to play with the parameters to make it work how you want, and add the text- I don't have a 2007 box to tweak it on) Google to find code snippets that will allow your macro to determine which version of Excel it is running on, then conditionally run either the 2003 code or this 2007 code. ActiveSheet.Shapes.AddLabel( _ msoTextOrientationHorizontal, _ ActiveSheet.Range("D5").Left, _ ActiveSheet.Range("D5").Top, _ 96.75, _ 17.25).Select With Selection.Font .Name = "Arial" .Size = 10 End With "Quin" wrote: > I get a "Compile Error" when I try to run the code in Excel 2007. > > Quin
From: Quin on 11 Feb 2010 01:49
I ran the new code alone just to see if I could get a simple text box out of it on Excel 2007. It also generated a compile error. This is what I put into "Sheet1" Sub MakeTextBoxes() ActiveSheet.Shapes.AddLabel( _ msoTextOrientationHorizontal, _ ActiveSheet.Range("D5").Left, _ ActiveSheet.Range("D5").Top, _ 96.75, _ 17.25).Select With Selection.Font ..Name = "Arial" ..Size = 10 End With Next End Sub This code complains "compile error: Next without For". I don't know enough about VBA to fix the error or combine this code with the original solution you offered. I don't think the version of Excel is anything to do with these compile errors because I have opened this code in "Compatibility Mode" as well as native 2007 mode. I hope you can stick with me on this. I think the solution is on the right track and I would like to take it a step at a time. Perhaps to start, I would like to find a way to generate a single text box with the contents of A1 in it. Quin |