Floating Chart While Scrolling

[Tiger9136]

I have been searching for an answer for this fix but I have not had any luck. This is what I need help with:

1. I have created command buttons to scroll left and right in an excel sheet 2 columns at a time. It also keeps a column count is cell E40.

2. I have 4 charts, Locations K30, P30, J46, J61.

3. When i use the command button to shift the columns I want to shift the chart locations.
For example Scroll ToLeft:=2 chart locations become M30, R30, L46, L61.


Private Sub PREV_WEEK_Click()


ActiveWindow.SmallScroll ToRight:=-2
If Range("E40").Value = 1 Then
Exit Sub
End If
Range("E40").Value = Range("E40").Value - 2


End Sub


Private Sub NEXT_WEEK_Click()


ActiveWindow.SmallScroll ToRight:=2
Range("E40").Value = Range("E40").Value + 2


End Sub

 

[DanteAmor]

Change "Chart1", "Chart2" by the names of your charts

Sub PREV_WEEK_Click()
    ActiveWindow.SmallScroll ToRight:=-2
    If Range("E40").Value = 1 Then
        Exit Sub
    End If
    Range("E40").Value = Range("E40").Value - 2
    '
    izq1 = ActiveSheet.DrawingObjects("chart1").Left
    izq2 = ActiveSheet.DrawingObjects("chart2").Left
    If izq1 <= Columns(3).Left Then Exit Sub
    For Each col In Rows(1).Columns
        If izq1 >= col.Left And izq1 <= col.Offset(0, 1).Left Then
            ActiveSheet.DrawingObjects("chart1").Left = col.Offset(0, -2).Left + 10
        End If
        If izq2 >= col.Left And izq2 <= col.Offset(0, 1).Left Then
            ActiveSheet.DrawingObjects("chart2").Left = col.Offset(0, -2).Left + 10
            Exit For
        End If
    Next
End Sub
'
Sub NEXT_WEEK_Click()
    ActiveWindow.SmallScroll ToRight:=2
    Range("E40").Value = Range("E40").Value + 2
    '
    izq1 = ActiveSheet.DrawingObjects("chart1").Left
    izq2 = ActiveSheet.DrawingObjects("chart2").Left
    For Each col In Rows(1).Columns
        If izq1 >= col.Left And izq1 <= col.Offset(0, 1).Left Then
            ActiveSheet.DrawingObjects("chart1").Left = col.Offset(0, 2).Left + 10
        End If
        If izq2 >= col.Left And izq2 <= col.Offset(0, 1).Left Then
            ActiveSheet.DrawingObjects("chart2").Left = col.Offset(0, 2).Left + 10
            Exit For
        End If
    Next
End Sub