Charts - auto format the scale (x-axis) by cell ref?

[nancyo]

I tried a search, but cannot get this to work today.

Is there a way to change the format of an axis in a chart by referencing a specific cell?

By this, can the MAX or MIN on the scale be referenced to a specific cell?

 

[Mark W.]

Mark - WAY over my head....

Not really. Just a matter of creating a couple of Defined names, and inserting them into your chart's existing SERIES function.

=SERIES(,Sheet1!X_Axis,Sheet1!Y_Axis,1)

 

[nancyo]

I have a simple XY scatter .

I got the simplier macros to work for both the X and Y axes, but with buttons.

I will look into the other more complicated code / formulas and post back.

thanks.

 

[btadams]

nancyo,

the code I posted will only work with an XY scatterplot. For any other chart type you will need to create dynamic named ranges and insert them into the source data of the chart. Ther are some examples of this at the links below:

http://www.tushar-mehta.com/

Go to the section for Dynamic Charts

http://www.ozgrid.com/Excel/DynamicRanges.htm

http://www.cpearson.com/excel/named.htm

 

[tusharm]

As others have pointed out, you need a programmatic solution. For a ready-made one, check the AutoChart Manager add-in available from my web site.

 

[nancyo]

I have never used the offset function, I will look into this.

I downloaded a bunch of stuff, and have a lot to read!

Thanks for all the references.

note: I still am getting a DEBUG error using btadams code above...?

 

[Mark W.]

BTW, another approach would be to have your MIN and MAX cell entries "feed" an criteria range for an Advanced AutoFilter. You can then set the Chart options (using Tools | Options... menu. command) to only plot visible cells.

 

[btadams]

nancyo,

if you have an xy scatterplot the code I posted should work. Try this: Open a new workbook and put some xy data and make a chart out of it (as an object in the worksheet). Then right-mouse click on the worksheet tab and choose View Code and paste the code below:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("G2") Or Target = Range("H2") Then
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart.Axes(xlCategory)
If Range("G2").Value <> "" Then .MinimumScale = Range("G2").Value
If Range("H2").Value <> "" Then .MaximumScale = Range("H2").Value
End With
End If
End Sub

Then when you change the values of G2 and H2 the chart x-axis should change accordingly.

 

[nancyo]

Well, you are right. It worked fine.

Now, I will go back and look at my file and see what's different.

I'll post back ASAP.

Thanks for hanging in there!

 

[nancyo]

OK - now it works fine for the X axis.

But, I copied / modified the code for the Y-axis (xlvalue), and when I enter values into these cells, the code doesn't work, and 'defaults' to the code for the X-axis. ???

Also, if i move around the spreadsheet, and randomly delete data from other cells, it triggers the x-axis macro, and a DEBUG. According to the help, the TARGET macros should not do this...?

 

[btadams]

can you post the code?