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

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

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

Quin

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

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

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