This code will take text from column A in an Excel Work sheet and insert it
into column B inside a text box. I would like to €œtweek€� this to skip over
the code that creates the text boxes on each row that contains empty text.
The result I seek is to have a text box created with text copied from column
A and no text box created when column A is empty.

My attempt to do this myself is not working out. I am just starting to
learn something about VBA. Any comments for a better approach to solve this
would be appreciated. I have commented out my attempt to skip past the
textbox creation code until it can be fixed.
Quin


Option Explicit

Sub MakeTextboxes()


Dim iLeft As Long
Dim iTop As Long
Dim iWidth As Long
Dim iHeight As Long
Dim iRow As Long

iLeft = Range("B1").Left
iWidth = Range("B1").Width

For iRow = 1 To 5
' If Cells(1, iRow).Value = "" Then GoTo Skip


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
'Skip:

Next iRow

End Sub

Hi Quin

The syntax for the Cells statement is :

Cells(Row, Column)

Also I always try to avoid goto statements, unless it is the only option:


Sub MakeTextboxes()
Dim iLeft As Long
Dim iTop As Long
Dim iWidth As Long
Dim iHeight As Long
Dim iRow As Long

iLeft = Range("B1").Left
iWidth = Range("B1").Width

For iRow = 1 To 5
If Trim(Cells(iRow, 1).Value) < "" Then
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
End If
Next iRow

End Sub

Regards,
Per

"Quin" skrev i meddelelsen
...
This code will take text from column A in an Excel Work sheet and insert
it
into column B inside a text box. I would like to €œtweek€� this to skip
over
the code that creates the text boxes on each row that contains empty text.
The result I seek is to have a text box created with text copied from
column
A and no text box created when column A is empty.

My attempt to do this myself is not working out. I am just starting to
learn something about VBA. Any comments for a better approach to solve
this
would be appreciated. I have commented out my attempt to skip past the
textbox creation code until it can be fixed.
Quin


Option Explicit

Sub MakeTextboxes()


Dim iLeft As Long
Dim iTop As Long
Dim iWidth As Long
Dim iHeight As Long
Dim iRow As Long

iLeft = Range("B1").Left
iWidth = Range("B1").Width

For iRow = 1 To 5
' If Cells(1, iRow).Value = "" Then GoTo Skip


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
'Skip:

Next iRow

End Sub

Hi,

Try this

Sub MakeTextboxes()
Dim iLeft As Long
Dim iTop As Long
Dim iWidth As Long
Dim iHeight As Long
Dim iRow As Long
iLeft = Range("B1").Left
iWidth = Range("B1").Width
For iRow = 1 To 5
If Range("A" & iRow).Value = "" Then GoTo Skip
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
Skip:
Next iRow
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Quin" wrote:

This code will take text from column A in an Excel Work sheet and insert it
into column B inside a text box. I would like to €œtweek€� this to skip over
the code that creates the text boxes on each row that contains empty text.
The result I seek is to have a text box created with text copied from column
A and no text box created when column A is empty.

My attempt to do this myself is not working out. I am just starting to
learn something about VBA. Any comments for a better approach to solve this
would be appreciated. I have commented out my attempt to skip past the
textbox creation code until it can be fixed.
Quin


Option Explicit

Sub MakeTextboxes()


Dim iLeft As Long
Dim iTop As Long
Dim iWidth As Long
Dim iHeight As Long
Dim iRow As Long

iLeft = Range("B1").Left
iWidth = Range("B1").Width

For iRow = 1 To 5
' If Cells(1, iRow).Value = "" Then GoTo Skip


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
'Skip:

Next iRow

End Sub

Mike H,

Thanks for fixing my code. I can see how the €œRange€� statement can be used
to accomplish my task. The result is exactly what I needed and wanted.


Jessen,

I found a lot of interesting information in your reply, but since I am
learning, I also have several questions. I hope you have a minute to answer.


1. I dont understand why it is frowned upon to use goto statements. It
seems like such an easy and straight forward thing to do.
2. I cant get my mind around using a < symbol. I tried to Google it with
no success.
3. I Googled €œTrim€� to find it removes leading and trailing spaces but not
sure why that helps in this case.
4. Im not sure why the text frame section is commented. Is that just to
use default settings when the text boxes are created?

My last observation is €œI must be Dyslexic€� Very poor of me to not notice my
confusion on the use of the €œCells€� statement. I appreciate your comment to
direct my attention to it.
Thank you Mike and Jessen

Quin

I just figured out what "Trim" does. It eliminates "spaces" in cells that
would otherwise create an unwanted text box. I will test that out...

"Quin" wrote:

Mike H,

Thanks for fixing my code. I can see how the €œRange€� statement can be used
to accomplish my task. The result is exactly what I needed and wanted.


Jessen,

I found a lot of interesting information in your reply, but since I am
learning, I also have several questions. I hope you have a minute to answer.


1. I dont understand why it is frowned upon to use goto statements. It
seems like such an easy and straight forward thing to do.
2. I cant get my mind around using a < symbol. I tried to Google it with
no success.
3. I Googled €œTrim€� to find it removes leading and trailing spaces but not
sure why that helps in this case.
4. Im not sure why the text frame section is commented. Is that just to
use default settings when the text boxes are created?

My last observation is €œI must be Dyslexic€� Very poor of me to not notice my
confusion on the use of the €œCells€� statement. I appreciate your comment to
direct my attention to it.
Thank you Mike and Jessen

Quin

Quin,

Thanks for your reply.

1. I understand that you find it straight forward to use Goto, but when you
start writing larger applications, you will find it harder to follow the
structure in the code, specially if your code need more than one goto
statement. I only use goto statements in error handling situations.

2. '<' (not equal to) is the opposite of '=' (equal to). So if the cell
tested is not empty, then proceed with next statement, else goto end if
statement.

3. Glad you figured out why I inserted the Trim statement.

4. The textbox margin section is commented out, because they are only needed
if you want to change default settings.

I hope this answered the questions.

Per

"Quin" skrev i meddelelsen
...
Mike H,

Thanks for fixing my code. I can see how the €œRange€� statement can be
used
to accomplish my task. The result is exactly what I needed and wanted.


Jessen,

I found a lot of interesting information in your reply, but since I am
learning, I also have several questions. I hope you have a minute to
answer.


1. I dont understand why it is frowned upon to use goto statements. It
seems like such an easy and straight forward thing to do.
2. I cant get my mind around using a < symbol. I tried to Google it
with
no success.
3. I Googled €œTrim€� to find it removes leading and trailing spaces but not
sure why that helps in this case.
4. Im not sure why the text frame section is commented. Is that just to
use default settings when the text boxes are created?

My last observation is €œI must be Dyslexic€� Very poor of me to not notice
my
confusion on the use of the €œCells€� statement. I appreciate your comment
to
direct my attention to it.
Thank you Mike and Jessen

Quin