Need help creating a graph that can automatically keep the x & y axis in the same scale

[chris.excel]

So i am trying to plot geographical data, and as such i need the x and y axis to be of the exact same scale. Furthermore, the area of the graph may change with each use, as i plot different sized areas of land. I managed to find this code (below) which looks like it should work, however im not really sure how to use it in this application. When i assign it to a graph either nothing happens, or i get an error saying that the .PlotArea has not been set. I'm no good at VBA haha, so just hoping someone could explain how i should integrate this into my graph.

Cheers,
Chris

Option Explicit

Sub MakePlotGridSquareOfActiveChart()
    MakePlotGridSquare ActiveChart
End Sub

Sub MakePlotGridSquareOfAllCharts()
    Dim myChartObject As ChartObject
    For Each myChartObject In ActiveSheet.ChartObjects
        MakePlotGridSquare myChartObject.Chart
    Next
End Sub

Sub MakePlotGridSquare(myChart As Chart, Optional bEquiTic As Boolean = False)
    
    Dim plotInHt As Integer, plotInWd As Integer
    Dim Ymax As Double, Ymin As Double, Ydel As Double
    Dim Xmax As Double, Xmin As Double, Xdel As Double
    Dim Ypix As Double, Xpix As Double

    With myChart
        ' get plot size
        With .PlotArea
            plotInHt = .InsideHeight
            plotInWd = .InsideWidth
        End With
        
        Do
            ' Get axis scale parameters and lock scales
            With .Axes(xlValue)
                Ymax = .MaximumScale
                Ymin = .MinimumScale
                Ydel = .MajorUnit
                .MaximumScaleIsAuto = False
                .MinimumScaleIsAuto = False
                .MajorUnitIsAuto = False
            End With
            With .Axes(xlCategory)
                Xmax = .MaximumScale
                Xmin = .MinimumScale
                Xdel = .MajorUnit
                .MaximumScaleIsAuto = False
                .MinimumScaleIsAuto = False
                .MajorUnitIsAuto = False
            End With
            If bEquiTic Then
                ' Set tick spacings to same value
                Xdel = WorksheetFunction.Max(Xdel, Ydel)
                Ydel = Xdel
                .Axes(xlCategory).MajorUnit = Xdel
                .Axes(xlValue).MajorUnit = Ydel
            End If
        
            ' Pixels per grid
            Ypix = plotInHt * Ydel / (Ymax - Ymin)
            Xpix = plotInWd * Xdel / (Xmax - Xmin)
            
            ' Keep plot size as is, adjust max scales
            If Xpix > Ypix Then
                .Axes(xlCategory).MaximumScale = plotInWd * Xdel / Ypix + Xmin
            Else
                .Axes(xlValue).MaximumScale = plotInHt * Ydel / Xpix + Ymin
            End If
            
            ' Repeat if "something" else changed to distort chart axes
            ' Don't repeat if we're within 1%
        Loop While Abs(Log(Xpix / Ypix)) > 0.01
        
    End With

End Sub

[millz]

From that chunk of code, seems like you only need:

    With myChart
        With .Axes(xlValue)
            .MinimumScale = yourMinScale
            .MaximumScale = yourMaxScale
        End With
        With .Axes(xlCategory)
            .MinimumScale = yourMinScale
            .MaximumScale = yourMaxScale
        End With
    End With

Amend and add to your existing code that creates the chart?

[chris.excel]

Thanks for the reply. This is part of my problem- the chart itself is not part of a macro, or created by one. It's a fixed object on the sheet if tat makes sense. I just need it to automatically scale as i input data (as standard graphs usually do) but keep the x/y axis ratio even at 1:1. However, i have no idea how to attribute VBA code to the graph to achieve this..

[millz]

Please post a sample workbook (with all sensitive data removed), showing where is the data being input, and with that chart.

Also, by what you described, do you also mean that the chart's source data should dynamically change as well?

[chris.excel]

Well if a dynamic table is what i think it is, then i don't think it's necessary as i have found a way around it (not a very nice way but it works). On that sheet, you can see that the graph picks up two sets of data using x1,y1 points and x2,y2 points


Attachment 282988