Chart updated from Worksheet_Calculate()

[zacbridgeman]

I am currently using the below code to update the scale for a chart on a different worksheet. My problem is that the cells F20, G20, and H20 are updated by a formula and thus will not update unless changed by actually retyping in the numbers that the formula calculated. I would like to see if I can use similar code as below with the Worksheet_calculate function instead of the Worksheet_Change.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Select Case Target.Address
Case "$F$20"
Sheets("Data entry sheet and tech graph").ChartObjects("Chart 1").Chart.Axes(xlValue) _
.MaximumScale = Target.Value
Case "$G$20"
Sheets("Data entry sheet and tech graph").ChartObjects("Chart 1").Chart.Axes(xlValue) _
.MinimumScale = Target.Value
Case "$H$20"
Sheets("Data entry sheet and tech graph").ChartObjects("Chart 1").Chart.Axes(xlCategory) _
.CrossesAt = Target.Value


Thank you in advance,

Zac

[Cougar548]

Just a thought, but I have created the same auto-updating chart using a different method. I am pulling data from about 10 different workbooks, but what I do is I create a new sheet that will gather all of this data. I will then create the chart on a separate sheet off of the new "data" worksheet just created. And since I don't want anyone to see this new "data" worksheet, cause it is generally pretty messy, I just hide it. That way no one will ever know. Don't know if this helps, but just thought I'd post my opinion.

[zacbridgeman]

Thanks for the reply but I chose to fix it with this code


-------In the sheet where the calculation occurs----------------
Private Sub Worksheet_Calculate()
Call macro111
End Sub
---------------------------------------------------------------------------


-------As a module-----------------------------------------------------
Sub macro111()
With Sheets("Data entry sheet and tech graph").ChartObjects("Chart 1").Chart

' Value (Y) Axis
With .Axes(xlValue)
.MaximumScale = Sheets("setupentry").Range("$F$20").Value
.MinimumScale = Sheets("setupentry").Range("$G$20").Value
.CrossesAt = Sheets("setupentry").Range("$H$20").Value
End With

End With

With Sheets("Data entry sheet and tech graph").ChartObjects("Chart 2").Chart

' Value (Y) Axis
With .Axes(xlValue)
.MaximumScale = Sheets("setupentry").Range("$F$21").Value
.MinimumScale = Sheets("setupentry").Range("$G$21").Value
.CrossesAt = Sheets("setupentry").Range("$H$21").Value
End With

End With

End Sub

------------------------------------------------------------------------------------------