Need to control multiple plot axis range with a variable or a designated cell location

[g_purve]

Thanks in advance for taking the time to help, you guys never let me down.
Little background on what I am doing and why its useful, if you don't care skip this paragraph. So I am a test engineer that tests small handheld engines and certifies them to EPA, CARB, EU, etc. I have been doing a lot of testing to quantify the impact of switching from 0% ethanol fuel to 10% ethanol fuel. In order to observe the impact of the different fuel properties on the engine we perform a fuel flow sweep which is where we adjust the carburetor from the lowest amount of fuel the engine can possibly run on to the highest amount of fuel. The impact the fuel has on the engine is monitored during the entire sweep and the data is averaged for each set point and presented in the locations starting at row 250 for series1 and row 266 for series2. The plots that I have created will update to display the data once pasted into the destinations.

Here is where I need some help: I am testing multiple engine families that have different ranges for the amount of fuel they require to operate. My x-axis range is changing depending on the engine I test. Can be anywhere from 90 to 2000. This requires me to have a specific template for the range of fuel flow values that I am testing over. I know I could do this by setting all of the plots in a single template to auto scale the x-axis but that doesn't give me the resolution required for conveying the results.​

So in a single question: Is it possible to set the x-axis of multiple plots to the same range using variable names or possibly a specific cell location?

 

[Worf]

Hi
This code allows you to scale the horizontal axis based on the values of two cells. If this is not what you need, please explain.

' at sheet module


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("r40:r41")) Is Nothing Then
    With Me.ChartObjects("Chart 1").Chart.Axes(xlCategory)
        .MinimumScale = Range("r40").Value
        .MaximumScale = Range("r41").Value
    End With
End If
End Sub

 

[g_purve]

Thanks for the response Worf. Unfortunately I get an error when trying to insert your code into the worksheet. The error is Compile error: Invalid use of Me keyword. Do I need to already have an object selected before trying to run this? Also what do you mean when you say " ' at sheet module"? Thanks, Garrett

 

[Jon Peltier]

Right click the worksheet tab that the chart is embedded on. A code module specific to that worksheet pops up. Paste the code there. It will run whenever any value in the worksheet changes, and if R40 or R41 is the cell that has changed, it will adjust the chart accordingly.

 

[g_purve]

Oh got it now, I was trying to put the code in a module. Thanks for the help. Is there a specific way to link the code to control multiple plots, like group a selection of plots to a variable name or possible rename all of the plots to the same common name?

 

[Worf]

This version will update all charts on that worksheet. Is this it?

' at sheet module


Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As ChartObject


If Not Intersect(Target, Range("r40:r41")) Is Nothing Then
    For Each c In Me.ChartObjects
        With c.Chart.Axes(xlCategory)
            .MinimumScale = Range("r40").Value
            .MaximumScale = Range("r41").Value
        End With
    Next
End If
End Sub

 

[g_purve]

Thanks Worf, that is just what I needed! Really admire how efficient the code is. It looks so simple after it is already written but not knowing the correct commands gets me every time.
Is the "Me" used as a generic reference to the worksheet currently activated?
Also why do you use the "If Not Intersect() Is Nothing code? Are you using this code to check if target range contains at least something before trying to adjust the axis?
Thanks again!

 

[Jon Peltier]

You'll get the hang of it after a while. We were all at your level once.

"Me" is in fact what is used in a code module to reference the object the code module backs up.

Intersect tells you what cells result from intersecting two ranges. You can't say

If Intersect(RangeA, RangeB) Is Something

but

If Not Intersect(RangeA, RangeB) Is Nothing

means the same thing.

 

[g_purve]

Thanks Jon for the info and for helping me out.