Excel Chart Moves Slightly Despite Setting Location

162 Views Asked by At

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

Added images

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
0

There are 0 best solutions below