Sub Ref_Click()
'set active sheet before running code
If ActiveSheet.Name <> "Ramp" Then
ThisWorkbook.Sheets("Ramp").Select
End If
Dim Shp As Shape
'delete current shapes except background and refresh button
For Each Shp In ActiveSheet.Shapes
If Shp.Name <> "Picture 1" And Shp.Name <> "Ref" Then Shp.Delete
Next Shp
Dim i As Integer
Dim status As String
Dim txt As String
Dim h As Long, w As Long
h = 65
w = 70
i = 2
For i = 1 To 20
If Sheet2.Range("b" & i) <> "" Then
'select correct color shape
If Sheet2.Range("b" & i).Offset(, 1).Value = "GOOD" Then 'if good
Sheet2.Shapes("Green").Copy 'copy green shape
GoTo position
ElseIf Sheet2.Range("b" & i).Offset(, 1).Value = "PARTIAL" Then 'if partial good
Sheet2.Shapes("Yellow").Copy 'copy yellow shape
GoTo position
ElseIf Sheet2.Range("b" & i).Offset(, 1).Value = "BROKE" Then 'if not good
Sheet2.Shapes("Red").Copy 'copy red shape
GoTo position
ElseIf Sheet2.Range("b" & i).Offset(, 1).Value = "TRANS" Then 'if trans
Sheet2.Shapes("Brown").Copy 'copy brown shape
position: 'figure out what spot to put the shape copy in
If Sheet2.Range("b" & i).Value = "HC" Then
ActiveSheet.Range("y38").Select
ActiveSheet.Paste
txt = Sheet2.Range("b" & i).Offset(, -1).Value
With Selection
.Name = txt
End With
Sheet1.Shapes(txt).TextFrame.Characters.Text = txt
Sheet1.Shapes(txt).TextFrame.HorizontalAlignment = xlHAlignCenter
Sheet1.Shapes(txt).TextFrame.VerticalAlignment = xlVAlignCenter
Sheet1.Shapes(txt).LockAspectRatio = msoFalse
Sheet1.Shapes(txt).Width = w
Sheet1.Shapes(txt).Height = h
Sheet1.Shapes(txt).Left = 460
Sheet1.Shapes(txt).Top = 545
ElseIf Sheet2.Range("b" & i).Value = "HE" Then
ActiveSheet.Range("ak33").Select
ActiveSheet.Paste
txt = Sheet2.Range("b" & i).Offset(, -1).Value
With Selection
.Name = txt
End With
Sheet1.Shapes(txt).TextFrame.Characters.Text = txt
Sheet1.Shapes(txt).TextFrame.HorizontalAlignment = xlHAlignCenter
Sheet1.Shapes(txt).TextFrame.VerticalAlignment = xlVAlignCenter
Sheet1.Shapes(txt).LockAspectRatio = msoFalse
Sheet1.Shapes(txt).Width = w
Sheet1.Shapes(txt).Height = h
Sheet1.Shapes(txt).Left = 700
Sheet1.Shapes(txt).Rotation = 90
Sheet1.Shapes(txt).Top = 450
ElseIf Sheet2.Range("b" & i).Value = "HW" Then
ActiveSheet.Range("af33").Select
ActiveSheet.Paste
txt = Sheet2.Range("b" & i).Offset(, -1).Value
With Selection
.Name = txt
End With
Sheet1.Shapes(txt).TextFrame.Characters.Text = txt
Sheet1.Shapes(txt).TextFrame.HorizontalAlignment = xlHAlignCenter
Sheet1.Shapes(txt).TextFrame.VerticalAlignment = xlVAlignCenter
Sheet1.Shapes(txt).LockAspectRatio = msoFalse
Sheet1.Shapes(txt).Width = w
Sheet1.Shapes(txt).Height = h
Sheet1.Shapes(txt).Left = 606
Sheet1.Shapes(txt).Rotation = -90
Sheet1.Shapes(txt).Top = 450
ElseIf Sheet2.Range("b" & i).Value = "ER7" Then
ActiveSheet.Range("m4").Select
ActiveSheet.Paste
txt = Sheet2.Range("b" & i).Offset(, -1).Value
With Selection
.Name = txt
End With
Sheet1.Shapes(txt).TextFrame.Characters.Text = txt
Sheet1.Shapes(txt).TextFrame.HorizontalAlignment = xlHAlignCenter
Sheet1.Shapes(txt).TextFrame.VerticalAlignment = xlVAlignCenter
Sheet1.Shapes(txt).LockAspectRatio = msoFalse
Sheet1.Shapes(txt).Width = w
Sheet1.Shapes(txt).Height = h
Sheet1.Shapes(txt).Left = 875
Sheet1.Shapes(txt).Rotation = 90
ElseIf Sheet2.Range("b" & i).Value = "ER1" Then
ActiveSheet.Range("au4").Select
ActiveSheet.Paste
txt = Sheet2.Range("b" & i).Offset(, -1).Value
With Selection
.Name = txt
End With
Sheet1.Shapes(txt).TextFrame.Characters.Text = txt
Sheet1.Shapes(txt).TextFrame.HorizontalAlignment = xlHAlignCenter
Sheet1.Shapes(txt).TextFrame.VerticalAlignment = xlVAlignCenter
Sheet1.Shapes(txt).LockAspectRatio = msoFalse
Sheet1.Shapes(txt).Width = w
Sheet1.Shapes(txt).Height = h
Sheet1.Shapes(txt).Left = 220
Sheet1.Shapes(txt).Rotation = -90
ElseIf Sheet2.Range("b" & i).Value = "1" Then
ActiveSheet.Range("m10").Select
ActiveSheet.Paste
txt = Sheet2.Range("b" & i).Offset(, -1).Value
With Selection
.Name = txt
End With
Sheet1.Shapes(txt).TextFrame.Characters.Text = txt
Sheet1.Shapes(txt).TextFrame.HorizontalAlignment = xlHAlignCenter
Sheet1.Shapes(txt).TextFrame.VerticalAlignment = xlVAlignCenter
Sheet1.Shapes(txt).LockAspectRatio = msoFalse
Sheet1.Shapes(txt).Width = w
Sheet1.Shapes(txt).Height = h
Sheet1.Shapes(txt).Left = 250
ElseIf Sheet2.Range("b" & i).Value = "2" Then
ActiveSheet.Range("t10").Select
ActiveSheet.Paste
txt = Sheet2.Range("b" & i).Offset(, -1).Value
With Selection
.Name = txt
End With
Sheet1.Shapes(txt).TextFrame.Characters.Text = txt
Sheet1.Shapes(txt).TextFrame.HorizontalAlignment = xlHAlignCenter
Sheet1.Shapes(txt).TextFrame.VerticalAlignment = xlVAlignCenter
Sheet1.Shapes(txt).LockAspectRatio = msoFalse
Sheet1.Shapes(txt).Width = w
Sheet1.Shapes(txt).Height = h
Sheet1.Shapes(txt).Left = 395
ElseIf Sheet2.Range("b" & i).Value = "3" Then
ActiveSheet.Range("x10").Select
ActiveSheet.Paste
txt = Sheet2.Range("b" & i).Offset(, -1).Value
With Selection
.Name = txt
End With
Sheet1.Shapes(txt).TextFrame.Characters.Text = txt
Sheet1.Shapes(txt).TextFrame.HorizontalAlignment = xlHAlignCenter
Sheet1.Shapes(txt).TextFrame.VerticalAlignment = xlVAlignCenter
Sheet1.Shapes(txt).LockAspectRatio = msoFalse
Sheet1.Shapes(txt).Width = w
Sheet1.Shapes(txt).Height = h
Sheet1.Shapes(txt).Left = 490
ElseIf Sheet2.Range("b" & i).Value = "4" Then
ActiveSheet.Range("ab10").Select
ActiveSheet.Paste
txt = Sheet2.Range("b" & i).Offset(, -1).Value
With Selection
.Name = txt
End With
Sheet1.Shapes(txt).TextFrame.Characters.Text = txt
Sheet1.Shapes(txt).TextFrame.HorizontalAlignment = xlHAlignCenter
Sheet1.Shapes(txt).TextFrame.VerticalAlignment = xlVAlignCenter
Sheet1.Shapes(txt).LockAspectRatio = msoFalse
Sheet1.Shapes(txt).Width = w
Sheet1.Shapes(txt).Height = h
Sheet1.Shapes(txt).Left = 585
ElseIf Sheet2.Range("b" & i).Value = "5" Then
ActiveSheet.Range("af10").Select
ActiveSheet.Paste
txt = Sheet2.Range("b" & i).Offset(, -1).Value
With Selection
.Name = txt
End With
Sheet1.Shapes(txt).TextFrame.Characters.Text = txt
Sheet1.Shapes(txt).TextFrame.HorizontalAlignment = xlHAlignCenter
Sheet1.Shapes(txt).TextFrame.VerticalAlignment = xlVAlignCenter
Sheet1.Shapes(txt).LockAspectRatio = msoFalse
Sheet1.Shapes(txt).Width = w
Sheet1.Shapes(txt).Height = h
Sheet1.Shapes(txt).Left = 680
ElseIf Sheet2.Range("b" & i).Value = "6" Then
ActiveSheet.Range("aj10").Select
ActiveSheet.Paste
txt = Sheet2.Range("b" & i).Offset(, -1).Value
With Selection
.Name = txt
End With
Sheet1.Shapes(txt).TextFrame.Characters.Text = txt
Sheet1.Shapes(txt).TextFrame.HorizontalAlignment = xlHAlignCenter
Sheet1.Shapes(txt).TextFrame.VerticalAlignment = xlVAlignCenter
Sheet1.Shapes(txt).LockAspectRatio = msoFalse
Sheet1.Shapes(txt).Width = w
Sheet1.Shapes(txt).Height = h
Sheet1.Shapes(txt).Left = 775
ElseIf Sheet2.Range("b" & i).Value = "7" Then
ActiveSheet.Range("an10").Select
ActiveSheet.Paste
txt = Sheet2.Range("b" & i).Offset(, -1).Value
With Selection
.Name = txt
End With
Sheet1.Shapes(txt).TextFrame.Characters.Text = txt
Sheet1.Shapes(txt).TextFrame.HorizontalAlignment = xlHAlignCenter
Sheet1.Shapes(txt).TextFrame.VerticalAlignment = xlVAlignCenter
Sheet1.Shapes(txt).LockAspectRatio = msoFalse
Sheet1.Shapes(txt).Width = w
Sheet1.Shapes(txt).Height = h
Sheet1.Shapes(txt).Left = 870
End If
End If
End If
Next i
ActiveSheet.Range("bk1").Select ' time updated
Selection = Now()
Application.OnTime Now + TimeValue("00:10:00"), "Ref_Click" ' timer refresh
hell:
End Sub