Hi,
I have below macro which work successfully and created required box and loop through all the data
However, when I add below macro it bugs out "run-time error 424, Object required", can you help me fixing this macro?
I have below macro which work successfully and created required box and loop through all the data
Code:
Sub add_EMPLOYEE_rectangles()Set aSht = ActiveSheet
Set rSht = Sheets("Role Scorecard")
rSht.Activate
Call reset_EMPLOYEE_rectangles
Dim galaxyF As Shape
Dim Empl_Lbl, Empl_Txt As Shape
Set galaxyF = rSht.Shapes("Galaxy_frame")
Dim eCnt As Long, e As Long, pix As Long
pix = 72 'use to convert inches to pixels
eCnt = 17 'number of data boxes in Employee Info section
Dim el, et, ew, eh As Single
Dim etl, ett, etw, eth As Single
'''BUILD THE eCnt LABEL BOX'''
For e = 1 To eCnt
ew = galaxyF.Width / 2
eh = galaxyF.Height / eCnt
el = galaxyF.Left
et = galaxyF.Top + (eh * (e - 1))
Set Empl_Lbl = rSht.Shapes.AddShape(msoShapeRectangle, el, et, ew, eh)
With Empl_Lbl
.Name = "Empl_" & e & "_Lbl"
.Placement = xlFreeFloating
.Fill.ForeColor.RGB = RGB(255, 255, 255)
.Fill.Visible = msoFalse
.Line.ForeColor.RGB = RGB(100, 100, 100)
.Line.Visible = False
.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
.TextFrame2.TextRange.Font.Size = 8
.TextFrame2.TextRange.Font.Name = "Tahoma"
.TextFrame.MarginLeft = 0.05 * pix
.TextFrame.MarginRight = 0.15 * pix
.TextFrame.MarginTop = 0.05 * pix
.TextFrame.MarginBottom = 0.05 * pix
.TextFrame2.TextRange.ParagraphFormat.Alignment = msoAlignLeft
.TextFrame2.VerticalAnchor = msoAnchorMiddle
.TextFrame2.AutoSize = msoAutoSizeNone
.TextFrame.VerticalOverflow = xlOartVerticalOverflowOverflow
.TextFrame.HorizontalOverflow = xlOartHorizontalOverflowOverflow
.TextFrame2.WordWrap = msoTrue
Dim shp As Shape
Dim lblRng As Range
Set lblRng = [LabelRange]
i = 0
For lbl = lblRng.Row To (lblRng.Row + lblRng.Rows.Count - 1)
i = i + 1
For Each Empl_Lbl In ActiveSheet.Shapes
If InStr(1, Empl_Lbl.Name, i) > 0 Then
Set shp = ActiveSheet.Shapes(i)
.TextFrame2.TextRange.Characters.Text = Cells(lbl, lblRng.Column).Value
.TextFrame2.TextRange.Font.Bold = msoTrue
End If
Next Empl_Lbl
Next lbl
End With
aSht.Activate
Next
End Sub
However, when I add below macro it bugs out "run-time error 424, Object required", can you help me fixing this macro?
Code:
'''BUILD THE eCnt TEXT BOX'''
etw = galaxyF.Width - Empl_Lbl.Width
eth = Empl_Lbl.Height
etl = Empl_Lbl.Left + Empl_Lbl.Width
ett = Empl_Lbl.Top
Set Empl_Txt = rSht.Shapes.AddShape(msoShapeRectangle, etl, ett, etw, eth)
With Empl_Txt
.Name = "Empl_" & e & "_Txt"
.Placement = xlFreeFloating
.Fill.ForeColor.RGB = RGB(255, 255, 255)
.Fill.Visible = msoFalse
.Line.ForeColor.RGB = RGB(100, 100, 100)
.Line.Visible = False
.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
.TextFrame2.TextRange.Font.Size = 8
.TextFrame2.TextRange.Font.Name = "Tahoma"
.TextFrame.MarginLeft = 0.15 * pix
.TextFrame.MarginRight = 0.05 * pix
.TextFrame.MarginTop = 0.05 * pix
.TextFrame.MarginBottom = 0.05 * pix
.TextFrame2.TextRange.ParagraphFormat.Alignment = msoAlignLeft
.TextFrame2.VerticalAnchor = msoAnchorMiddle
.TextFrame2.AutoSize = msoAutoSizeNone
.TextFrame.VerticalOverflow = xlOartVerticalOverflowOverflow
.TextFrame.HorizontalOverflow = xlOartHorizontalOverflowOverflow
.TextFrame2.WordWrap = msoTrue
.TextFrame2.TextRange.Characters.Text = "TEXT " & Format(e, "00000")
.TextFrame2.TextRange.Font.Bold = msoFalse
End With
Last edited by a moderator: