title: excel vba code to run a macro from a cell formula
body: <p>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.</p>
  
  <p>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?!</p>
  
  <p>Many thanks!</p>
  
  <pre><code>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
  </code></pre>