Get the automatic maximum y-axis bounds value and paste in cell

[Dbroek]

On the attached image, you will see the maximum y-axis value automatically listed as 30.0. What can be done for a worksheet cell (ex. A1) to automatically list this number? I need this number to set my phase line maximum.
Please help!

Screenshot 2022-06-09 163647.png

[AskMeAboutExcel]

Two options I'm aware of:
1) Use a VBA script to read this from the chart and paste into a cell
2) Calculate manually based on your dataset used by the graph, following the same logic as Excel does. A description of how excel chooses these values is at https://peltiertech.com/how-excel-ca...t-axis-limits/

[Dbroek]

I'm trying to figure out what the VBA script would be. The closest I got was use the following debug.print function to print the maximum y scale value into the immediate window in code.

Please Login or Register  to view this content.

I can get the correct automatic value that the chart generates into the immediate code window when I run this code. But how do I get this value placed, for example, in cell U4 on the worksheet? I need this value in order to resize my phase line.
Thanks

[MrShorty]

Use the Range.Value property to write a value to a cell.

Please Login or Register  to view this content.

Range.value help file: https://docs.microsoft.com/en-us/off...el.range.value

[Dbroek]

I don't know how to write the code to use this.

[MrShorty]

Try putting Sub test()..End Sub around it, maybe a Stop statement so it will enter debug mode, then see what happens when you run it:

Please Login or Register  to view this content.

paste that into a regular module, then make sure the desired Excel sheet is active, then place the cursor anywhere inside of the procedure and press F5 or Run->Run procedure. Use F8 to step through the procedure, watching what each statement does.

[AskMeAboutExcel]

but to answer your question directly i.e. how to get the value and paste into cell U4, simply use:

Please Login or Register  to view this content.

or to make it even more concise....

Please Login or Register  to view this content.

[Dbroek]

Thanks MrShorty, for sticking with this issue.
I did paste the code into the module using the following format:

Please Login or Register  to view this content.

After that, went to the worksheet where the chart is located and ran this code there. it came up with a msgbox indicating a compile error: Variable not defined. "dblmaxscale =" was highlighted.
So, maybe dblmaxscale is not being recognized as a definable variable?

I'd like to make this work, if possible, just to get a better understanding of what's we're trying to accomplish.

[MrShorty]

it came up with a msgbox indicating a compile error: Variable not defined. "dblmaxscale =" was highlighted.

That suggests you have Option Explicit specified, which usually just means you need to add a DIM statement defining all variables, or, as noted by AskMeAboutExcel, bypass storing the max scale value in a variable.

[Dbroek]

I finally got the dblmaxscale code to work rewritten as this:

Please Login or Register  to view this content.

But had to add the ".MaximumScale" to the middle line. I'm guessing this was inadvertently left out?

[Dbroek]

AskMeAboutExcel,
Both of your codes worked from the start, and performed as well as the dblmaxscale code I rewrote.
All three, however, failed to place the max Y value into the U4 cell whenever I deselected one of the datasets with the larger max value (for example, 72) and kept selected a dataset containing a smaller max value (for example, 12). It is jammed up, somehow, by the phase line (see dataset series 11 in code below).

Below is an example of what I had to rewrite to free things up so the codes can do their jobs:

Please Login or Register  to view this content.

It's pretty clunky and bangs the chart around a bit. Not exactly sure what to do next to smooth this out a bit more. But, at least, it accomplishes what I wanted to see.
Thank you both for taking some of your time to help on this problem. I'm so grateful!

[AskMeAboutExcel]

Dbroek - that's strange, although I'm not sure why it's having that behaviour.

If you do need to filter / unfilter to get the right value, then you should put the U4 = max value logic at the end of your code, not the start though.

Perhaps you can share a sanitised version of the worksheet and we can try help you get to the bottom of this? Or is your current workaround fit for purpose so no further support needed?

[Dbroek]

Here's the CountTest file.
I changed to have the macro run when the button in O4 is changed instead of using a SelectionChange macro.

I suspect that the U4 cell needs to automatically be refreshed whenever the series data is being changed on the chart.

[AskMeAboutExcel]

does the chart only change when you select the dropdown options in Cells E4 and H4?

If so, you can add the two lines highlighted in blue below to the Worksheet Change Event for 'Sheet 7 (Day1_5)', to run concurrently with updating the chart categories

Please Login or Register  to view this content.

[Dbroek]

That doesn't work. I still have to hit the form control button to align the phase line properly with the rest of the data sets.

For example, if you have data1 selected to graph on the chart and then deselect it and have only data2 and data3 selected to be graphed, the phase line will stay stuck at 76. If you then hit the form control button, it will drop down to 57, which is where we want it to be. The phase line occurs on the 5/17/22 date and takes its form from the 1_5 worksheet.

[Dbroek]

Playing around with Record Macro I noticed something interesting that could possibly lead to a solution.

The following macro:

Please Login or Register  to view this content.

runs the getYAxisMax code after deselecting the phase line data set and then reselects the phase line data set while the chart is activated. This allows the Phase Line to accept the new value that the getYAxisMax placed in the U4 cell. I noticed that when I ran the Macro1 code immediately after deselecting or selecting another data set inside the chart, for example...

Please Login or Register  to view this content.

the phase line readapts smoothly to the correct value without causing the chart to bounce like it does when hitting the form control button.

Is it possible to have a code that automatically runs Macro1 or maybe the following

Please Login or Register  to view this content.

whenever any of the available ActiveChart.FullSeriesCollection(X).IsFiltered false or true? It will be a dream if that could be done.