Automate Copy and Paste?

[Quin]

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

 

[ker_01]

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]

I get a "Compile Error" when I try to run the code in Excel 2007.

Quin

 

[ker_01]

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]

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

 

[K_Macd]

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).

 

[Quin]

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

 

[ker_01]

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

 

[Quin]

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.

 

[Quin]

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 Card.jpg
Thanks!

 

[Quin]

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(msoTextOrientationHorizontal, _
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