Prev: Can a hyperlink run a macro?
Next: using lookup function in vba
From: K_Macd kmacdonald "A_T" activ8 ''D O T'' net [ST OP] on 11 Feb 2010 07:36
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
From: Quin on 11 Feb 2010 12:43
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


From: ker_01 on 11 Feb 2010 17:14
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
>
>
From: Quin on 11 Feb 2010 17:36
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.


From: Quin on 14 Feb 2010 22:26
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!

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Can a hyperlink run a macro?
Next: using lookup function in vba