XY Scatter - Switch between Auto-scaling and Manual scaling referencing cell values

[mikec798]

First time poster...

I have a XY Scatter Chart acting as a project timeline and want to allow individuals to zoom in on sections of the chart (Manual mode) and then switch back to the Excel auto-scale (Auto mode). I've added a tick box that when ticked it should auto-scale the chart and if un-ticked it should use values in certain cells and the chart should update as the values in the cells change.

I've got the chart on a sheet called Dashboard and the cells for the Manual mode are on a sheet called Worksheet Variables.

I've done lots of digging and playing around, but just can't seem to get it to work. I've got the code living on the Dashboard Excel Object as a Change command (don't know if that is the right place for it though).

Example: When the tick box on sheet "Dashboard" is ticked cell U2 on Worksheet Variables is True and the chart should auto-scale. If Worksheet Variables cell U2 is false then the chart should scale to the values in the sheet "Worksheet Variables" cells U7, U8, U9, V2, V3, V4 and then the chart should update as those values change.

Here's what I've got so far; any help would be brilliant!

Private Sub Worksheet_Change(ByVal Target As Range)

Dim wsWV As Worksheet


Set wsWV = Worksheets("Worksheet Variables")


    Select Case wsWV.Range("$U$2").Value
    
        Case True
                ChartObjects("Timeline").Chart.Axes(xlCategory).MaximumScaleIsAuto = True
                ChartObjects("Timeline").Chart.Axes(xlCategory).MinimumScaleIsAuto = True
                ChartObjects("Timeline").Chart.Axes(xlCategory).MajorUnitIsAuto = True
                ChartObjects("Timeline").Chart.Axes(xlValue).MaximumScaleIsAuto = True
                ChartObjects("Timeline").Chart.Axes(xlValue).MinimumScaleIsAuto = True
                ChartObjects("Timeline").Chart.Axes(xlValue).MajorUnitIsAuto = True
        Case Else
            Case wsWV.Range("$U$7")
                    ChartObjects("Timeline").Chart.Axes(xlCategory).MinimumScale = Target.Value
            Case wsWV.Range("$U$8")
                    ChartObjects("Timeline").Chart.Axes(xlCategory).MaximumScale = Target.Value
            Case wsWV.Range("$U$9")
                    ChartObjects("Timeline").Chart.Axes(xlCategory).MajorUnit = Target.Value
            Case wsWV.Range("$V$3")
                    ChartObjects("Timeline").Chart.Axes(xlValue).MinimumScale = Target.Value
            Case wsWV.Range("$V$2")
                    ChartObjects("Timeline").Chart.Axes(xlValue).MaximumScale = Target.Value
            Case wsWV.Range("$V$4")
                    ChartObjects("Timeline").Chart.Axes(xlValue).MajorUnit = Target.Value
    End Select
End Sub

Thanks!

 

[tusharm]

Use the below in the worksheet variables code module:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Application.Intersect( _
        Target, Range("u2,u7:U9,v2:v4")) Is Nothing Then Exit Sub
    
    Dim aChart As Chart: Set aChart = Worksheets("Dashboard").ChartObjects("Timeline").Chart
    If Range("$U$2").Value Then
        With aChart
        .Axes(xlCategory).MaximumScaleIsAuto = True
        .Axes(xlCategory).MinimumScaleIsAuto = True
        .Axes(xlCategory).MajorUnitIsAuto = True
        .Chart.Axes(xlValue).MaximumScaleIsAuto = True
        .Chart.Axes(xlValue).MinimumScaleIsAuto = True
        .Chart.Axes(xlValue).MajorUnitIsAuto = True
            End With
    Else
        With aChart
        .Axes(xlCategory).MinimumScale = Range("$U$7").Value
        .Axes(xlCategory).MaximumScale = Range("$U$8").Value
        .Axes(xlCategory).MajorUnit = Range("$U$9").Value
        .Axes(xlValue).MinimumScale = Range("$V$3").Value
        .Axes(xlValue).MaximumScale = Range("$V$2").Value
        .Axes(xlValue).MajorUnit = Range("$V$4").Value
            End With
        End If
    End Sub

First time poster...

I have a XY Scatter Chart acting as a project timeline and want to allow individuals to zoom in on sections of the chart (Manual mode) and then switch back to the Excel auto-scale (Auto mode). I've added a tick box that when ticked it should auto-scale the chart and if un-ticked it should use values in certain cells and the chart should update as the values in the cells change.

I've got the chart on a sheet called Dashboard and the cells for the Manual mode are on a sheet called Worksheet Variables.

I've done lots of digging and playing around, but just can't seem to get it to work. I've got the code living on the Dashboard Excel Object as a Change command (don't know if that is the right place for it though).

Example: When the tick box on sheet "Dashboard" is ticked cell U2 on Worksheet Variables is True and the chart should auto-scale. If Worksheet Variables cell U2 is false then the chart should scale to the values in the sheet "Worksheet Variables" cells U7, U8, U9, V2, V3, V4 and then the chart should update as those values change.

Here's what I've got so far; any help would be brilliant!

Private Sub Worksheet_Change(ByVal Target As Range)

Dim wsWV As Worksheet


Set wsWV = Worksheets("Worksheet Variables")


    Select Case wsWV.Range("$U$2").Value
    
        Case True
                ChartObjects("Timeline").Chart.Axes(xlCategory).MaximumScaleIsAuto = True
                ChartObjects("Timeline").Chart.Axes(xlCategory).MinimumScaleIsAuto = True
                ChartObjects("Timeline").Chart.Axes(xlCategory).MajorUnitIsAuto = True
                ChartObjects("Timeline").Chart.Axes(xlValue).MaximumScaleIsAuto = True
                ChartObjects("Timeline").Chart.Axes(xlValue).MinimumScaleIsAuto = True
                ChartObjects("Timeline").Chart.Axes(xlValue).MajorUnitIsAuto = True
        Case Else
            Case wsWV.Range("$U$7")
                    ChartObjects("Timeline").Chart.Axes(xlCategory).MinimumScale = Target.Value
            Case wsWV.Range("$U$8")
                    ChartObjects("Timeline").Chart.Axes(xlCategory).MaximumScale = Target.Value
            Case wsWV.Range("$U$9")
                    ChartObjects("Timeline").Chart.Axes(xlCategory).MajorUnit = Target.Value
            Case wsWV.Range("$V$3")
                    ChartObjects("Timeline").Chart.Axes(xlValue).MinimumScale = Target.Value
            Case wsWV.Range("$V$2")
                    ChartObjects("Timeline").Chart.Axes(xlValue).MaximumScale = Target.Value
            Case wsWV.Range("$V$4")
                    ChartObjects("Timeline").Chart.Axes(xlValue).MajorUnit = Target.Value
    End Select
End Sub

Thanks!

 

[mikec798]

Thanks tusharm. This seems to be sort of working.

My X Axis values are populated by the user selecting the Start Date using the Microsoft Date and Time Picker Control 6.0 and then choose a time period such as "1 Week", "1 Month", etc. from a combo box. The Date Picker is linked to cell U7 and then in U8 I have a formula that takes the Start Date plus the length of the time period they chose.

The issue appears to be that the cells need to be manually edited rather than the value just changing via the Date Picker cell link and formula. Is there a way to simulate this manual edit via vba?

 

[mikec798]

I figured it out. I needed to change my tickbox from a form control to an ActiveX control.
Thanks for your help.

 

[tusharm]

It's good you got that sorted out. To play safe, ensure that the range references are explicitly for the Worksheet Variables sheet.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WS As Worksheet: Set WS = Target.Parent
    If Application.Intersect( _
        Target, WS.Range("u2,u7:U9,v2:v4")) Is Nothing Then Exit Sub
    
    Dim aChart As Chart: Set aChart = Worksheets("Dashboard").ChartObjects("Timeline").Chart
    If WS.Range("$U$2").Value Then
        With aChart
        .Axes(xlCategory).MaximumScaleIsAuto = True
        .Axes(xlCategory).MinimumScaleIsAuto = True
        .Axes(xlCategory).MajorUnitIsAuto = True
        .Chart.Axes(xlValue).MaximumScaleIsAuto = True
        .Chart.Axes(xlValue).MinimumScaleIsAuto = True
        .Chart.Axes(xlValue).MajorUnitIsAuto = True
            End With
    Else
        With aChart
        .Axes(xlCategory).MinimumScale = WS.Range("$U$7").Value
        .Axes(xlCategory).MaximumScale = WS.Range("$U$8").Value
        .Axes(xlCategory).MajorUnit = WS.Range("$U$9").Value
        .Axes(xlValue).MinimumScale = WS.Range("$V$3").Value
        .Axes(xlValue).MaximumScale = WS.Range("$V$2").Value
        .Axes(xlValue).MajorUnit = WS.Range("$V$4").Value
            End With
        End If
    End Sub

I figured it out. I needed to change my tickbox from a form control to an ActiveX control.
Thanks for your help.