Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Sub Draw_All()
- Clear
- 'Draws a box on each sheet for given dims
- Dim i As Integer
- Dim ws_count As Integer
- Dim ssz1lb As Shape
- Dim ssz2lb As Shape
- Dim ssz3lb As Shape
- Dim ssz4lb As Shape
- Dim ssz5lb As Shape
- Dim box As Shape
- Dim sz1 As Shape
- Dim sz2 As Shape
- Dim seam As Shape
- Dim seam2t As Shape
- Dim seam2b As Shape
- Dim slb As Shape
- Dim s2tlb As Shape
- Dim s2blb As Shape
- Dim c1 As Range
- Dim w As Range
- Dim h As Range
- Dim s As Range
- Dim s1t As Range
- Dim s1b As Range
- Dim s1 As Range
- Dim s2t As Range
- Dim s2m As Range
- Dim s2b As Range
- Dim z As Range
- ws_count = Sheets.Count
- For i = 2 To ws_count
- Set c1 = Worksheets(i).Range("B44") 'start point
- Set w = Worksheets(i).Range("D22") 'width
- Set h = Worksheets(i).Range("H22") 'height
- Set s = Worksheets(i).Range("K17") 'seam number
- Set s1 = Worksheets(i).Range("M22") 'seam location
- Set s1t = Worksheets(i).Range("H25") 'Seam 1 top size
- Set s1b = Worksheets(i).Range("H28") 'Seam 1 Bottom Size
- Set s2t = Worksheets(i).Range("H31") 'seam 2 top size
- Set s2m = Worksheets(i).Range("H34") 'seam 2 middle size
- Set s2b = Worksheets(i).Range("H37") 'seam 2 bottom size
- Set z = Worksheets(i).Range("O44") 'Scale
- Set box = Worksheets(i).Shapes.AddShape(1, c1.Left, c1.Top, w, h)
- box.LockAspectRatio = msoTrue
- box.Height = h * z.Value
- 'Box Height Label
- Set sz1 = Worksheets(i).Shapes.AddTextbox(2, c1.Left - 17, c1.Top + (h * 0.5) * z.Value - 50, 15, 100)
- sz1.Line.Visible = msoFalse
- sz1.TextFrame.HorizontalAlignment = xlHAlignCenter
- sz1.TextFrame.VerticalAlignment = xlVAlignCenter
- sz1.TextFrame.Characters.Font.Size = 14
- sz1.TextFrame.Characters.Text = h.Text
- 'Box Width Label
- Set sz2 = Worksheets(i).Shapes.AddTextbox(1, c1.Left + (w * 0.5) * z.Value - 50, c1.Top - 17, 100, 15)
- sz2.Line.Visible = msoFalse
- sz2.TextFrame.HorizontalAlignment = xlHAlignCenter
- sz2.TextFrame.VerticalAlignment = xlVAlignCenter
- sz2.TextFrame.Characters.Font.Size = 14
- sz2.TextFrame.Characters.Text = w.Text
- ' For Seam 1 Line
- If s = 1 Then
- Set seam = Worksheets(i).Shapes.AddLine(c1.Left, c1.Top + (h - s1) * z.Value, c1.Left + w * z.Value + 5, c1.Top + (h - s1) * z.Value)
- seam.Line.DashStyle = msoLineDash
- seam.Line.Weight = 1
- seam.Line.ForeColor.RGB = RGB(0, 164, 239)
- Set slb = Worksheets(i).Shapes.AddTextbox(1, c1.Left + w * z.Value + 5, c1.Top + (h - s1) * z.Value - 5, 60, 10)
- slb.Line.Visible = msoFalse
- slb.TextFrame.VerticalAlignment = xlVAlignCenter
- slb.TextFrame.Characters.Font.Size = 9
- slb.TextFrame.Characters.Font.Color = RGB(0, 164, 239)
- slb.TextFrame.Characters.Text = s1.Text
- slb.TextFrame.MarginLeft = 5
- ' Top Seam 1 Section Label
- Set ssz1lb = Worksheets(i).Shapes.AddTextbox(1, c1.Left + w * z.Value * 0.5 - 60, c1.Top + ((h - s1) * 0.5) * z.Value - 7, 120, 15)
- ssz1lb.Line.Visible = msoFalse
- ssz1lb.Fill.Transparency = 0.2
- ssz1lb.TextFrame.HorizontalAlignment = xlHAlignCenter
- ssz1lb.TextFrame.VerticalAlignment = xlVAlignCenter
- ssz1lb.TextFrame.Characters.Font.Size = 11
- ssz1lb.TextFrame.Characters.Font.Color = RGB(105, 105, 105)
- ssz1lb.TextFrame.Characters.Text = (h - s1) & "'' + 1'' =" & s1t.Text
- 'Bottom Seam 1 Section Label
- Set ssz2lb = Worksheets(i).Shapes.AddTextbox(1, c1.Left + w * z.Value * 0.5 - 60, c1.Top + (h - (s1 * 0.5)) * z.Value - 7, 120, 15)
- ssz2lb.Fill.Transparency = 0.2
- ssz2lb.Line.Visible = msoFalse
- ssz2lb.TextFrame.HorizontalAlignment = xlHAlignCenter
- ssz2lb.TextFrame.VerticalAlignment = xlVAlignCenter
- ssz2lb.TextFrame.Characters.Font.Size = 11
- ssz2lb.TextFrame.Characters.Font.Color = RGB(105, 105, 105)
- ssz2lb.TextFrame.Characters.Text = (s1) & "'' + 1'' = " & s1b.Text
- ElseIf s = 2 Then
- 'Set Top seam
- Set seam2t = Worksheets(i).Shapes.AddLine(c1.Left, c1.Top + (s2t - 1) * z.Value, c1.Left + w * z.Value + 5, c1.Top + (s2t - 1) * z.Value)
- seam2t.Line.DashStyle = msoLineDash
- seam2t.Line.Weight = 1
- seam2t.Line.ForeColor.RGB = RGB(0, 164, 239)
- Set s2tlb = Worksheets(i).Shapes.AddTextbox(1, c1.Left + w * z.Value + 5, c1.Top + (s2t - 1) * z.Value - 5, 50, 10)
- s2tlb.Line.Visible = msoFalse
- s2tlb.TextFrame.VerticalAlignment = xlVAlignCenter
- s2tlb.TextFrame.Characters.Font.Size = 9
- s2tlb.TextFrame.Characters.Font.Color = RGB(0, 164, 239)
- s2tlb.TextFrame.Characters.Text = (s2m - 2) & " '' "
- s2tlb.TextFrame.MarginLeft = 5
- 'Set Bottom Seam
- Set seam2b = Worksheets(i).Shapes.AddLine(c1.Left, c1.Top + (h - s2b - 1) * z.Value, c1.Left + w * z.Value + 5, c1.Top + (h - s2b - 1) * z.Value)
- seam2b.Line.DashStyle = msoLineDash
- seam2b.Line.Weight = 1
- seam2b.Line.ForeColor.RGB = RGB(0, 164, 239)
- Set s2blb = Worksheets(i).Shapes.AddTextbox(1, c1.Left + w * z.Value + 5, c1.Top + (h - s2b - 1) * z.Value - 5, 50, 10)
- s2blb.Line.Visible = msoFalse
- s2blb.TextFrame.VerticalAlignment = xlVAlignCenter
- s2blb.TextFrame.Characters.Font.Size = 9
- s2blb.TextFrame.Characters.Font.Color = RGB(0, 164, 239)
- s2blb.TextFrame.Characters.Text = (s2b - 1) & " '' "
- s2blb.TextFrame.MarginLeft = 5
- 'Bottom Seam 2 Section Label
- Set ssz3lb = Worksheets(i).Shapes.AddTextbox(1, c1.Left + w * z.Value * 0.5 - 50, c1.Top + (h - (s2b * 0.5)) * z.Value - 7, 120, 15)
- ssz3lb.Line.Visible = msoFalse
- ssz3lb.Fill.Transparency = 0.2
- ssz3lb.TextFrame.HorizontalAlignment = xlHAlignCenter
- ssz3lb.TextFrame.VerticalAlignment = xlVAlignCenter
- ssz3lb.TextFrame.Characters.Font.Size = 11
- ssz3lb.TextFrame.Characters.Font.Color = RGB(105, 105, 105)
- ssz3lb.TextFrame.Characters.Text = (s2b - 1) & "'' + 1'' = " & s2b.Text
- 'Middle Seam 2 Section Label
- Set ssz4lb = Worksheets(i).Shapes.AddTextbox(1, c1.Left + w * z.Value * 0.5 - 50, c1.Top + (s2t + (s2m * 0.5) - 7) * z.Value, 120, 15)
- ssz4lb.Line.Visible = msoFalse
- ssz4lb.Fill.Transparency = 0.2
- ssz4lb.TextFrame.HorizontalAlignment = xlHAlignCenter
- ssz4lb.TextFrame.VerticalAlignment = xlVAlignCenter
- ssz4lb.TextFrame.Characters.Font.Size = 11
- ssz4lb.TextFrame.Characters.Font.Color = RGB(105, 105, 105)
- ssz4lb.TextFrame.Characters.Text = (s2m - 2) & "'' + 2'' = " & s2m.Text
- 'Top Seam 2 Section Label
- Set ssz5lb = Worksheets(i).Shapes.AddTextbox(1, c1.Left + w * z.Value * 0.5 - 50, c1.Top + (s2t * 0.5) * z.Value - 7, 120, 15)
- ssz5lb.Line.Visible = msoFalse
- ssz5lb.Fill.Transparency = 0.2
- ssz5lb.TextFrame.HorizontalAlignment = xlHAlignCenter
- ssz5lb.TextFrame.VerticalAlignment = xlVAlignCenter
- ssz5lb.TextFrame.Characters.Font.Size = 11
- ssz5lb.TextFrame.Characters.Font.Color = RGB(105, 105, 105)
- ssz5lb.TextFrame.Characters.Text = (s2t - 1) & "'' + 1'' = " & s2t.Text
- End If
- Next i
- End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement