excel vba code to run a macro from a cell formula

Question

I have a workbook with one worksheet full of Data and another worksheet full of Charts. I have written some code which automatically changes the chart scales and units of every chart simultaneously based on the user entering the required values in two cells on the Chart worksheet. The values that they need to enter are on the Data sheet and are fairly lengthy formulae themselves. I would like to change the cells on the Charts worksheet to reference the values on the Data sheet so the charts automatically update when new data is added and these cells change, i.e. without the user having to manually do it. I have read a few other similar posts on here about working with formula and tried adapting the code but they are all a bit different to my situation and I haven't been able to get anything to work.

Below is the code I am currently using which works fine when values are entered manually. The values are in D6 and D7 on the Chart sheet, and I would like them to reference formulae in G4 and G5 on the Data sheet. Please can anyone tell me what extra code I need and where does it go?!

Many thanks!

Private Sub Worksheet_Change(ByVal target As Range)

Dim Cht As ChartObject
For Each Cht In ActiveSheet.ChartObjects
    Cht.Chart.Axes(xlValue).MaximumScale = [D6]
    Cht.Chart.Axes(xlValue).MajorUnit = [D7]
Next Cht

End Sub

If all data entry occurs on the Data sheet, you could minimize the chart updates by placing the update code in the Activate event of the chart worksheet. That way the update only runs when it's needed (i.e. when someone views the charts) — Tim Williams
Hurray! The Activate event worked perfectly. I guessed it would be something simple in the end. Thanks very much @TimWilliams for your help! — Sam Mitchell

Scott Holtzman Scott Holtzman · Accepted Answer · 2015-12-14T18:40:38

Move your code to the Worksheet_Calculate Event of your Chart sheet.

Once you set the cell values of D6 and D7 to equal the formula values on the Data sheet, this will work for you each time the formulas in the data sheet are recalculated, thus causing the D6 and D7 to recalculate. However, with this solution, bear in mind that this code will fire whenever the formulas in the data are recalculated, which may not be desirable.

Note- It's also best to qualify which sheet your range refers to. Which is why I added Me. in front of the range reference.

Private Sub Worksheet_Calculate()

Dim Cht As ChartObject
For Each Cht In ActiveSheet.ChartObjects
    Cht.Chart.Axes(xlValue).MaximumScale = Me.Range("D6")
    Cht.Chart.Axes(xlValue).MajorUnit = Me.Range("D7")
Next Cht

End Sub

excel vba code to run a macro from a cell formula

1 Answers