Adjust both axes on the left and right side of the graph

Kusini

New Member
Joined
Apr 17, 2012
Messages
29
I have a graph with a dual axes both primary and secondary Y-axis. I am trying to call the range for both axes from a table next to the graph, where is the values in the source table change the range is calculated in the table next to the graph and the graph takes up the range defines in the scale adjust box. I have included a link to the document. If you are able to guide me in developing a macro I would appreciate.



Document Link:- https://www.box.com/s/5bc7ff7fd5118203ded2
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Al Chara

MrExcel MVP
Joined
Feb 21, 2002
Messages
1,701
See if this works for you. Note! You must place this code in the worksheet's code module.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Application.Intersect(Target, Range("M15:N16")) Is Nothing Then Exit Sub
    ActiveSheet.ChartObjects("Chart 2").Activate
    With ActiveChart
        With .Axes(xlValue)
            .MinimumScale = Range("M15")
            .MaximumScale = Range("M16")
        End With
        With .Axes(xlValue, xlSecondary)
            .MinimumScale = Range("N15")
            .MaximumScale = Range("N16")
        End With
    End With
End Sub
 

Kusini

New Member
Joined
Apr 17, 2012
Messages
29
Hi Chara,

Thanks for the macro it works quite well I just have one challenge. I have three graphs in the same worksheet, I tried adjusting the macro but it kept giving me an error. If you don't mind I have uploaded the new file with all three graphs if you don't mind show me how I can adjust, I tried to change the graph reference to chart 2 and chart 3 but this didn't work also.

Adjusted File: - https://www.box.com/s/41155f5883cf5c37763c

Thanks,

Regards.
 

Al Chara

MrExcel MVP
Joined
Feb 21, 2002
Messages
1,701
For now, I have hardcoded the cell references into the code, as you only have three charts. If you ever want to add many more charts, then I would suggest changing the logic of the code, as it might become combersome to update.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Application.Intersect(Target, Range("M15:N20")) Is Nothing Then Exit Sub
    
    ActiveSheet.ChartObjects("Chart 2").Activate
    With ActiveChart
        With .Axes(xlValue)
            .MinimumScale = Range("M15")
            .MaximumScale = Range("M16")
        End With
        With .Axes(xlValue, xlSecondary)
            .MinimumScale = Range("N15")
            .MaximumScale = Range("N16")
        End With
    End With
    
    ActiveSheet.ChartObjects("Chart 3").Activate
    With ActiveChart
        With .Axes(xlValue)
            .MinimumScale = Range("M17")
            .MaximumScale = Range("M18")
        End With
        With .Axes(xlValue, xlSecondary)
            .MinimumScale = Range("N17")
            .MaximumScale = Range("N18")
        End With
    End With
    
    ActiveSheet.ChartObjects("Chart 4").Activate
    With ActiveChart
        With .Axes(xlValue)
            .MinimumScale = Range("M19")
            .MaximumScale = Range("M20")
        End With
        With .Axes(xlValue, xlSecondary)
            .MinimumScale = Range("N19")
            .MaximumScale = Range("N20")
        End With
    End With
    
End Sub
 

Kusini

New Member
Joined
Apr 17, 2012
Messages
29

ADVERTISEMENT

Hi Chara,

Thanks for the adjustment, I just had one more question. Is this only possible if I have one sheet in the workbook? I tried to run the macro on a workbook but I could not see the macro as its not visible. Any suggestions, can I add the macro in a module and reference the specific work sheet? Also if I have more than three graphs how do I name the different graphs.

Thanks
 

Kusini

New Member
Joined
Apr 17, 2012
Messages
29
Hi Chara,

Thanks for the adjustment, I just had one more question. Is this only possible if I have one sheet in the workbook? I tried to run the macro on a workbook but I could not see the macro as its not visible. Any suggestions, can I add the macro in a module and reference the specific work sheet? Also if I have more than three graphs how do I name the different graphs.

Thanks
 

Al Chara

MrExcel MVP
Joined
Feb 21, 2002
Messages
1,701
You originally requested that the macro be triggered by a change in certain cells, so I used the WorkSheet_Change event. If you want to put the code in a standard module and call it manually, then that can work as well. As the code is written today, it references the ActiveSheet, so when you call it, make sure that the sheet containing the charts is active.

If you will have a variable number of graphs, then you might want to consider putting the repeating code into a loop.
 

Forum statistics

Threads
1,137,154
Messages
5,679,912
Members
419,862
Latest member
Bluewings666

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top