Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate Copy and Paste?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate Copy and Paste?
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(msoTextOrientationHo rizontal, 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate Copy and Paste?
I get a "Compile Error" when I try to run the code in Excel 2007.
Quin |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate Copy and Paste?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate Copy and Paste?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate Copy and Paste?
For and Next are paired control statements which means that any code within
them will be repeated whilst the next record meets the earlier For condition. This contruction is a very common concept in all programing languages. There is no For statement in your most recent code. Ker 01's initial response gives a good example of For/Next use. For what its worth I always have VBA Help open when I am writing code so I can do quick sorts on any key word (2007 is better than earlier versions). -- Ken "Using Dbase dialects since 82" "Started with Visicalc in the same year" "Quin" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate Copy and Paste?
Using K Macd's tips I was able to create a blank text box. I tried as best I
could to put the code snippits together to have text from Column A go into the text box but it only creates the empty text box. Here is what I put together. 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.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 At least it compiles now... Quin |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate Copy and Paste?
The second code I posted was specific to the issue you had with Excel 2007,
but was not intended to be a complete solution (it was provided so you could integrate it with the code from post #1 to create a complete solution). If you are just trying to get this to work in 2007, then you just need to assign the value to the textbox: 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.AddLabel( _ msoTextOrientationHorizontal, _ ActiveSheet.Range("D5").Left, _ ActiveSheet.Range("D5").Top, _ 96.75, _ 17.25).Select Selection.text = tText With Selection.Font .Name = "Arial" .Size = 10 End With Next End Sub "Quin" wrote: Using K Macd's tips I was able to create a blank text box. I tried as best I could to put the code snippits together to have text from Column A go into the text box but it only creates the empty text box. Here is what I put together. 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.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 At least it compiles now... Quin |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate Copy and Paste?
Yes, that is why I inserted the excel 2007 code into the original code you
provided. Unfortunately I apparently do not understand exactly what is required because it only makes an empty text box when I run it. I don't know how to assign the value to the text box. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate Copy and Paste?
I Will explain my project€¦
I have about 300 €śInstruction€ť cards for an equal number of employees. Each card has multiple lines of instruction that are currently in Cells of an Excel spread sheet. Management has decided that each task needs to be listed in a new instruction card €śGANTT€ť style. In other words each task will be represented by a text box on a time scale, size adjusted to the amount of time the task requires. My approach is to copy each instruction from the cells and place them into text boxes. (Since there are two columns of information I will €śConcatenate€ť first). Once the information is in text boxes I will manually distribute them according to the time assigned. I will also delete unneeded text boxes. All this should make sense after you view the attached link. You will see that the entire project depends upon my ability to get lots of lines of text into excel text boxes (not cells). The left side of the attached card is the €śold€ť style of assigning tasks. The right side is the new desired GANTT style. This is another reason that we will need to designate quantity of text boxes in this program rather than specify a column. I need a way to stop the creation of text boxes when we run out of cards to process. I will be happy to further describe this project if needed, but for now, the details of the project are not important other than I need to copy many lines of text (A1 through A1000) into many Excel Text boxes. B1 through B1000. If I do not get help with this code I will need to copy and paste manually thousands of lines of text into text boxes. With some expert help it will take 5 minutes. Please take a look at this link.. http://members.cox.net/cquinc/Example%20Card.jpg Thanks! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate Copy and Paste?
A Very helpful Expert on another message board answered my question and
provided some code to create text boxes with text from column A. I am grateful to him. It is exactly what I needed. For others that might have a need for this information I will share the code here. This VBA Looks in column A. Copies any value into a new textbox which is the exact size of the cell in column B. Creates 1000 text boxes, no matter if there's a value in A or not. ___________________________________ Sub MakeTextboxes() Dim iLeft As Integer Dim iTop As Integer Dim iWidth As Integer Dim iHeight As Integer Dim sTheString As String Dim iRow As Long Dim timeStart As Date Dim timeStop As Date Dim HeightMultiplier As Double HeightMultiplier = 1.2 iLeft = Range("B1").Left iWidth = Range("B1").Width For iRow = 1 To 1000 iTop = Range("b" & iRow).Top iHeight = Range("b" & iRow).Height With ActiveSheet.Shapes.AddTextbox(msoTextOrientationHo rizontal, _ iLeft, iTop, iWidth, iHeight) .TextFrame.Characters.Text = Range("a" & iRow) .TextFrame.MarginBottom = 0 .TextFrame.MarginLeft = 0 .TextFrame.MarginRight = 0 .TextFrame.MarginTop = 0 End With Next iRow End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automate copy / paste | Excel Discussion (Misc queries) | |||
automate copy and paste | Excel Discussion (Misc queries) | |||
Automate Copy and Paste | Excel Programming | |||
Any (clever) way to automate ROW:COL in copy/paste??? | Excel Worksheet Functions | |||
Automate Cut/Copy/Paste from Excel Cells | Excel Programming |