"OP" ????? ... I don't get you !
How could I show the code when I don't have it on that time ?
Here is a better solution ... call with - lets say 1900,2018 ... works nicely:
Public Sub timeLine(startYear As Integer, endYear As Integer)
Const leftSpace = 200
Const yearSpace = 100
Const stickOut = 20
Const yearBoxWidth = 38
Dim arrow As Shape, yearBox As Shape, x As Integer, subDivider As Shape, subX As Integer
If startYear <= endYear Then
' first draw the timeline as an arrow the length of (endYear-startYear+1)*yearSpace
Set arrow = ActiveSheet.Shapes.AddConnector(msoConnectorStraight, leftSpace - stickOut, 100, _
leftSpace + (endYear - startYear + 1) * yearSpace + 1.8 * stickOut, 100)
With arrow
.Line.EndArrowheadStyle = msoArrowheadOpen
.Name = "Timeline"
End With
Set arrow = Nothing
' then insert the years and some subdividers
Set subDivider = ActiveSheet.Shapes.AddConnector(msoConnectorStraight, leftSpace, 90, leftSpace, 100)
Set subDivider = Nothing
For x = 1 To endYear - startYear + 1
Set yearBox = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, leftSpace + (x - 1) * yearSpace + yearSpace / 2 - yearBoxWidth / 2, _
75, yearBoxWidth, 20)
subX = leftSpace + x * yearSpace
Set subDivider = ActiveSheet.Shapes.AddConnector(msoConnectorStraight, subX, 90, subX, 100)
'.LineStyle = xlLineStyleNone
With yearBox
.TextFrame.Characters.Text = startYear - 1 + x
.Line.Visible = msoFalse
End With
Set yearBox = Nothing
Set subDivider = Nothing
Next x
End If
End Sub