Advertisement
KySoto

Reformatted your code

Jul 23rd, 2019
278
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.     Sub Draw_All()
  2.         Clear
  3.  
  4.         'Draws a box on each sheet for given dims
  5.         Dim i As Integer
  6.         Dim ws_count As Integer
  7.  
  8.         Dim ssz1lb As Shape
  9.         Dim ssz2lb As Shape
  10.         Dim ssz3lb As Shape
  11.         Dim ssz4lb As Shape
  12.         Dim ssz5lb As Shape
  13.         Dim box As Shape
  14.         Dim sz1 As Shape
  15.         Dim sz2 As Shape
  16.         Dim seam As Shape
  17.         Dim seam2t As Shape
  18.         Dim seam2b As Shape
  19.         Dim slb As Shape
  20.         Dim s2tlb As Shape
  21.         Dim s2blb As Shape
  22.  
  23.         Dim c1 As Range
  24.         Dim w As Range
  25.         Dim h As Range
  26.         Dim s As Range
  27.         Dim s1t As Range
  28.         Dim s1b As Range
  29.         Dim s1 As Range
  30.         Dim s2t As Range
  31.         Dim s2m As Range
  32.         Dim s2b As Range
  33.         Dim z As Range
  34.        
  35.         ws_count = Sheets.Count
  36.  
  37.         For i = 2 To ws_count
  38.  
  39.  
  40.  
  41.             Set c1 = Worksheets(i).Range("B44")      'start point
  42.             Set w = Worksheets(i).Range("D22")       'width
  43.             Set h = Worksheets(i).Range("H22")       'height
  44.             Set s = Worksheets(i).Range("K17")       'seam number
  45.             Set s1 = Worksheets(i).Range("M22")      'seam location
  46.             Set s1t = Worksheets(i).Range("H25")     'Seam 1 top size
  47.             Set s1b = Worksheets(i).Range("H28")     'Seam 1 Bottom Size
  48.             Set s2t = Worksheets(i).Range("H31")     'seam 2 top size
  49.             Set s2m = Worksheets(i).Range("H34")     'seam 2 middle size
  50.             Set s2b = Worksheets(i).Range("H37")     'seam 2 bottom size
  51.             Set z = Worksheets(i).Range("O44")       'Scale
  52.  
  53.  
  54.             Set box = Worksheets(i).Shapes.AddShape(1, c1.Left, c1.Top, w, h)
  55.             box.LockAspectRatio = msoTrue
  56.             box.Height = h * z.Value
  57.        
  58.             'Box Height Label
  59.             Set sz1 = Worksheets(i).Shapes.AddTextbox(2, c1.Left - 17, c1.Top + (h * 0.5) * z.Value - 50, 15, 100)
  60.             sz1.Line.Visible = msoFalse
  61.             sz1.TextFrame.HorizontalAlignment = xlHAlignCenter
  62.             sz1.TextFrame.VerticalAlignment = xlVAlignCenter
  63.             sz1.TextFrame.Characters.Font.Size = 14
  64.             sz1.TextFrame.Characters.Text = h.Text
  65.             'Box Width Label
  66.             Set sz2 = Worksheets(i).Shapes.AddTextbox(1, c1.Left + (w * 0.5) * z.Value - 50, c1.Top - 17, 100, 15)
  67.             sz2.Line.Visible = msoFalse
  68.             sz2.TextFrame.HorizontalAlignment = xlHAlignCenter
  69.             sz2.TextFrame.VerticalAlignment = xlVAlignCenter
  70.             sz2.TextFrame.Characters.Font.Size = 14
  71.             sz2.TextFrame.Characters.Text = w.Text
  72.        
  73.             ' For Seam 1 Line
  74.             If s = 1 Then
  75.  
  76.                 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)
  77.                 seam.Line.DashStyle = msoLineDash
  78.                 seam.Line.Weight = 1
  79.                 seam.Line.ForeColor.RGB = RGB(0, 164, 239)
  80.            
  81.                 Set slb = Worksheets(i).Shapes.AddTextbox(1, c1.Left + w * z.Value + 5, c1.Top + (h - s1) * z.Value - 5, 60, 10)
  82.                 slb.Line.Visible = msoFalse
  83.                 slb.TextFrame.VerticalAlignment = xlVAlignCenter
  84.                 slb.TextFrame.Characters.Font.Size = 9
  85.                 slb.TextFrame.Characters.Font.Color = RGB(0, 164, 239)
  86.                 slb.TextFrame.Characters.Text = s1.Text
  87.                 slb.TextFrame.MarginLeft = 5
  88.  
  89.                 ' Top Seam 1 Section Label
  90.                 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)
  91.                 ssz1lb.Line.Visible = msoFalse
  92.                 ssz1lb.Fill.Transparency = 0.2
  93.                 ssz1lb.TextFrame.HorizontalAlignment = xlHAlignCenter
  94.                 ssz1lb.TextFrame.VerticalAlignment = xlVAlignCenter
  95.                 ssz1lb.TextFrame.Characters.Font.Size = 11
  96.                 ssz1lb.TextFrame.Characters.Font.Color = RGB(105, 105, 105)
  97.                 ssz1lb.TextFrame.Characters.Text = (h - s1) & "'' + 1'' =" & s1t.Text
  98.        
  99.                 'Bottom Seam 1 Section Label
  100.                 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)
  101.                 ssz2lb.Fill.Transparency = 0.2
  102.                 ssz2lb.Line.Visible = msoFalse
  103.                 ssz2lb.TextFrame.HorizontalAlignment = xlHAlignCenter
  104.                 ssz2lb.TextFrame.VerticalAlignment = xlVAlignCenter
  105.                 ssz2lb.TextFrame.Characters.Font.Size = 11
  106.                 ssz2lb.TextFrame.Characters.Font.Color = RGB(105, 105, 105)
  107.                 ssz2lb.TextFrame.Characters.Text = (s1) & "'' + 1'' = " & s1b.Text
  108.  
  109.             ElseIf s = 2 Then
  110.                 'Set Top seam
  111.                 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)
  112.                 seam2t.Line.DashStyle = msoLineDash
  113.                 seam2t.Line.Weight = 1
  114.                 seam2t.Line.ForeColor.RGB = RGB(0, 164, 239)
  115.            
  116.                 Set s2tlb = Worksheets(i).Shapes.AddTextbox(1, c1.Left + w * z.Value + 5, c1.Top + (s2t - 1) * z.Value - 5, 50, 10)
  117.                 s2tlb.Line.Visible = msoFalse
  118.                 s2tlb.TextFrame.VerticalAlignment = xlVAlignCenter
  119.                 s2tlb.TextFrame.Characters.Font.Size = 9
  120.                 s2tlb.TextFrame.Characters.Font.Color = RGB(0, 164, 239)
  121.                 s2tlb.TextFrame.Characters.Text = (s2m - 2) & " '' "
  122.                 s2tlb.TextFrame.MarginLeft = 5
  123.        
  124.                 'Set Bottom Seam
  125.                 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)
  126.                 seam2b.Line.DashStyle = msoLineDash
  127.                 seam2b.Line.Weight = 1
  128.                 seam2b.Line.ForeColor.RGB = RGB(0, 164, 239)
  129.            
  130.                 Set s2blb = Worksheets(i).Shapes.AddTextbox(1, c1.Left + w * z.Value + 5, c1.Top + (h - s2b - 1) * z.Value - 5, 50, 10)
  131.                 s2blb.Line.Visible = msoFalse
  132.                 s2blb.TextFrame.VerticalAlignment = xlVAlignCenter
  133.                 s2blb.TextFrame.Characters.Font.Size = 9
  134.                 s2blb.TextFrame.Characters.Font.Color = RGB(0, 164, 239)
  135.                 s2blb.TextFrame.Characters.Text = (s2b - 1) & " '' "
  136.                 s2blb.TextFrame.MarginLeft = 5
  137.        
  138.                 'Bottom Seam 2 Section Label
  139.                 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)
  140.                 ssz3lb.Line.Visible = msoFalse
  141.                 ssz3lb.Fill.Transparency = 0.2
  142.                 ssz3lb.TextFrame.HorizontalAlignment = xlHAlignCenter
  143.                 ssz3lb.TextFrame.VerticalAlignment = xlVAlignCenter
  144.                 ssz3lb.TextFrame.Characters.Font.Size = 11
  145.                 ssz3lb.TextFrame.Characters.Font.Color = RGB(105, 105, 105)
  146.                 ssz3lb.TextFrame.Characters.Text = (s2b - 1) & "'' + 1'' = " & s2b.Text
  147.        
  148.                 'Middle Seam 2 Section Label
  149.                 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)
  150.                 ssz4lb.Line.Visible = msoFalse
  151.                 ssz4lb.Fill.Transparency = 0.2
  152.                 ssz4lb.TextFrame.HorizontalAlignment = xlHAlignCenter
  153.                 ssz4lb.TextFrame.VerticalAlignment = xlVAlignCenter
  154.                 ssz4lb.TextFrame.Characters.Font.Size = 11
  155.                 ssz4lb.TextFrame.Characters.Font.Color = RGB(105, 105, 105)
  156.                 ssz4lb.TextFrame.Characters.Text = (s2m - 2) & "'' + 2'' = " & s2m.Text
  157.        
  158.                 'Top Seam 2 Section Label
  159.                 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)
  160.                 ssz5lb.Line.Visible = msoFalse
  161.                 ssz5lb.Fill.Transparency = 0.2
  162.                 ssz5lb.TextFrame.HorizontalAlignment = xlHAlignCenter
  163.                 ssz5lb.TextFrame.VerticalAlignment = xlVAlignCenter
  164.                 ssz5lb.TextFrame.Characters.Font.Size = 11
  165.                 ssz5lb.TextFrame.Characters.Font.Color = RGB(105, 105, 105)
  166.                 ssz5lb.TextFrame.Characters.Text = (s2t - 1) & "'' + 1'' = " & s2t.Text
  167.  
  168.             End If
  169.         Next i
  170.  
  171.     End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement