Format chart axis options by cell reference? Is it possible?

[frabulator]

So I know this is possible through VBA, but I would like an option to to create a dynamic scatter plot that changes the axis options to automatically adjust to the values specified in a cell.
Like I said, VBA it is entirely possible, but I would like this to be an option with just formulas/values/referencing. Is this possible?

Thanks,
Frab

 

[James006]

Hi,
You can test Named Ranges ...
see:

Use defined names to automatically update a chart range - Microsoft 365 Apps

Provides two methods for using defined names to automatically update a chart range. You can use these methods to set up a chart that can be automatically updated when you add new information to an existing chart range.

learn.microsoft.com

 

[frabulator]

Hi,
You can test Named Ranges ...
see:

Use defined names to automatically update a chart range - Microsoft 365 Apps

Provides two methods for using defined names to automatically update a chart range. You can use these methods to set up a chart that can be automatically updated when you add new information to an existing chart range.

learn.microsoft.com

Not exactly what I was looking for.

That will update the values in a range that is being graphed. IE: set the graph to plot a range, change the values in the range, values in graph changes.

I was wondering if I could update the distance that is being plotted/shown. For instance, can I specify that C2 is going to be the Minimum X Axes value while C3 is the Maximum X Axes value?

In VBA it is pretty simple, just manually change it to whatever. I could also work the long way around and reduce the values being plotted by the range that I am wanting to limit.

Is it possible to do this with just a cell reference?

 

[awoohaw]

I'm not a VBA expert. But, if you don't mind VBA to start you could put the options that vba would use in cells on the worksheet and then use the worksheet change event to adjust the chart options when any of those cells change.

 

[frabulator]

I'm not a VBA expert. But, if you don't mind VBA to start you could put the options that vba would use in cells on the worksheet and then use the worksheet change event to adjust the chart options when any of those cells change.

Yes you can. In fact, here is an example of how that can be done.

The below will gather the current min/max, resize them, and then populate the min/max back to original.

Sub whatis()

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.PlotArea.Select
    Dim os As Double
    os = ActiveChart.Axes(xlValue).MinimumScale
    Dim ot As Double
    ot = ActiveChart.Axes(xlCategory).MinimumScale
    
    For i = 1 To 25
        DoEvents
        
        Dim dp As Double
        dp = i
        ActiveChart.Axes(xlValue).MinimumScale = dp * -1
        ActiveChart.Axes(xlValue).MaximumScale = dp
        
        dp = i
  
    Next
    
    ActiveChart.Axes(xlValue).MinimumScale = os
    ActiveChart.Axes(xlValue).MaximumScale = os * -1
    
    
    ActiveChart.Axes(xlCategory).MinimumScale = ot
    ActiveChart.Axes(xlCategory).MaximumScale = ot * -1
End Sub

Like I said, I can do it in VBA, but can I do it without?