Q
Quin
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(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
'Skip:
Next iRow
End Sub
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(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
'Skip:
Next iRow
End Sub