Macro so capture value from WS then use to set scale on char

[PYStraube]

I would like to create a macro that would capture values (that change periodically) on a tab then use them to set the scale for both the X & Y axis of an Excel XP chart. I have a couple dozen charts in this file that woudl need this done. If I understand correctly, this would require VBA coding instead of basic macro programming.
Any help would be appreciated.

[kkknie]

I'm not sure what the difference between VBA coding and basic macro programming is (they mean the same thing to me), but I'll start with quick response.

You can change the ranges of a chart by setting the MaximumScale and MinimumScale properties. Here is an example:

Sub SetChartAxes()

    

   Dim dblHighX As Double

   Dim dblLowX As Double

   Dim dblHighY As Double

   Dim dblLowY As Double

   

'

'  First set up your limits

'

   dblHighX = Sheets("Sheet1").Range("A1").Value

   dblLowX = Sheets("Sheet1").Range("A2").Value

   dblHighY = Sheets("Sheet1").Range("B1").Value

   dblLowY = Sheets("Sheet1").Range("B2").Value



'

'  This section assumes your chart is a sheet by itself

'

   Sheets("Chart1").Select

   With ActiveChart.Axes(xlValue)

       .MinimumScale = dblLowY

       .MaximumScale = dblHighY

   End With

   With ActiveChart.Axes(xlCategory)

       .MinimumScale = dblLowX

       .MaximumScale = dblHighX

   End With

      

'

'  This section assumes your chart is embedded in a worksheet

'

   Sheets("Chart1").ChartObjects("Chart 1").Select

   With ActiveChart.Axes(xlValue)

       .MinimumScale = dblLowY

       .MaximumScale = dblHighY

   End With

   With ActiveChart.Axes(xlCategory)

       .MinimumScale = dblLowX

       .MaximumScale = dblHighX

   End With

   

End Sub

Note that your sheet names and chart names will hopefully be different. Try that out for a start...

K

P.S. One good way to troubleshoot this code (or create your own) is to record a macro doing a manual setting of the axes and see what names were used.

[PYStraube]

Thanks for your help with this . I really appreciate it.