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

[Kusini]

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

 

[Al Chara]

See if this works for you. Note! You must place this code in the worksheet's code module.

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]

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]

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.

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]

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]

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]

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.

 

[Kusini]

Hi Chara,

Here is the full file with the Macro, thanks for the input I tried to adjust it but it didn't work let me know if i should do it in a different way

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

Thanks.