Use VBA to change scale of y axis of a chart

[Greenbriars]

Although I am a competent Excel user, my knowledge of VBA is minimal. I wonder if someone can help me with a problem.

I have a workbook with 3 sheets.

1. Control
2. Data Input
3. Chart (it just happens to be of weight and some other data)

I use the control sheet to enter fixed data to be used in the chart. Titles, height, target BMI and some other stuff which get passed either to the data input sheet or to the chart itself.

The data input sheet is simply a table which records dates and weight and potentially comments. The table is also fed by the control sheet to record target BMI and to take the potential comments and add data so that the comment, date, weight and BMI can be displayed on the chart as a label.

The chart is self explanatory (I think). It is on a Chart Sheet, rather than being an embedded object on a worksheet.

What I want to achieve is to calculate a Y axis max and min in the control sheet, based on data in the data input sheet and use the max and min to scale the Y axis. I have no problem getting the correct max and min to appropriate cells in the control sheet.

I can use VBA to change the scale if the max and min data are on a worksheet with an embedded chart, but I have no idea how to change the scale based on data in another sheet.

Can somebody help me please?

Many thanks.

 

[iferror]

I am just guessing here....but it may likely be that you're not referencing the Chart properly.
If this is the case, this is just a hint that could help you find the right direction. Should you need further assistance do not hesitate to ask

Give this macro a try

Sub SheetvsChart()
Dim wsht As Worksheet
Dim chrt As Chart

For Each wsht In ActiveWorkbook.Worksheets
  Debug.Print wsht.Name
Next

For Each chrt In ActiveWorkbook.Charts
  Debug.Print chrt.Name
Next

End Sub

Whenever a chart is not embedded in a worksheet, it can be accessed as Workbook object.

HTH

 

[Debraj]

Hi Alan!
Welcome to the forum!
Can you please upload your sheet.... for better understanding..

BTW.. both type of chart are approx same..
Embedded chart are basically object of SHAPE, where Chart sheet are object of SHEET.

in the meantime.. try this..

sheets("Chart1").axes(xlvalue).maximumscale = 200

 

[Greenbriars]

Thanks for help so far. I will try to upload the file. The Y axis max and min are calculated and are in cells B17 & B18 on the Control sheet. The idea is that when this works all that can be input is in the yellow cells of the Control sheet and columns A, B & C of the Data Input sheet. Then a person with no Excel experience can print out the chart without having to worry about scaling. It is important that the lowest data point is as close to the X axis as possible to make the graph slopes more apparent.

 

[Debraj]

Please check the attached..

Is this what you are looking for..

 

[Greenbriars]

Debraj

This is just what I wanted. Thanks very much indeed.

I had tried to do this by using a change event in the Control worksheet and then trying to reference the chart. As I said I could make this work if the chart and input were on the same sheet.

I just love that this solution is so simple and elegant. I must spend time getting to grips with VBA!

Chandoo is correct. You Ninjas are awesome.

Many thanks once again

 

[Debraj]

Thanks for the feedback..

 

[Greenbriars]

Many thanks again for helping me with my chart.

Having added the VBA script to the original chart I now have a problem which was not there before. I don't think that the script can be causing it, however.

The input sheet was designed so that only cells with comments entered would generate labels which would display on the chart.

Now when I view or print the chart I can see labels for each data point. Only the labels with comments are populated correctly, but there are small label outlines for all the data points.

I don't think that I have done anything to the file other than paste in your script.

Any ideas?

Regards.

Alan