I have created a Gantt chart that sits directly next to the row the data is pulled from so everything is in one neat line. There are several months of data, so I have created two buttons (Next Page/Previous Page) that add/subtract 30 from the minimum and maximum values of the x-axis. Because everything is lined up Gantt-table-style, I have been trying to reset the chart's location to it's original one. Unfortunately, the chart still moves creating a small gap between the chart and my data. Any advice is greatly appreciated!
None of my charts corners lay on the corner of a cell, plus some columns may be re sized later on.
Private Sub NEXT_PG_Click()
Dim chart_height As Single, chart_length As Single, chart_top As Single, chart_left As Single
'Saving original position to save later
With ActiveSheet.ChartObjects("Chart 2")
chart_height = .Height
chart_width = .Width
chart_top = .Top
chart_left = .Left
End With
'Displaying next 30 days of data
With ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue)
.MinimumScale = .MinimumScale + 30
.MaximumScale = .MaximumScale + 30
End With
'Trying to set original location and size
With ActiveSheet.ChartObjects("Chart 2")
.Height = chart_height
.Width = chart_width
.Left = chart_left
.Top = chart_top
End With
End Sub
Update: As a temporary fix, I have tried to set the location of the plot area to a specific location, but this makes it worse. Please see the change to the attempted with statement below:
With ActiveSheet.ChartObjects("Chart 2").Chart.PlotArea
.Height = Range("A8:A30").Height
.Width = Range("J6:U6").Width
.Top = Range("J8").Top
.Left = Range("J8").Left
End With