Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Based on Chart Data Won't Recalculate
I'm using the following function to detect the size of the Y-axis in a chart that is autoscaled by Excel: Public Function MaxChartY() MaxChartY = Worksheets("CTest").ChartObjects.Item(1).Chart.Axe s(xlValue, xlPrimary).MaximumScale End Function Then, in a cell on the "CTest" sheet, I enter: =MaxChartY() When first entered, the formula correctly shows the Y-axis maximum value, but when data for the chart is changed and the Y-axis autoscales accordingly, my MaxChartY formula does not update. How can I get my formula to update automatically when the Y-axis autoscales because of a change in the data feeding the chart? -- ericsh ------------------------------------------------------------------------ ericsh's Profile: http://www.excelforum.com/member.php...o&userid=26149 View this thread: http://www.excelforum.com/showthread...hreadid=568574 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Based on Chart Data Won't Recalculate
I suggest you instead paste this to the worksheet's code module:
Private Sub Worksheet_Change(ByVal Target As Range) With Me.ChartObjects(1).Chart Range("C25").Value = .Axes(xlValue, xlPrimary).MaximumScale End With End Sub Functions (including UDFs) only update when cell values listed as arguments to the function change. Your formula does not directly reference any cell value but references the chart instead. If you use the statement Application.Volatile True within the UDF code structure then it will update whenever ANY cells change. However, the function unfortunately updates before the chart and consequentially is not in sync with the chart and returns the chart's old y-axis maximum scale value. For example, the following will return the old maximum scale value. Do not run it at the same time as the above Worksheet_Change event code or it will conflict: Function MaxChartY() Application.Volatile True MaxChartY = ActiveSheet.ChartObjects(1).Chart. _ Axes(xlValue, xlPrimary).MaximumScale End Function Regards, Greg "ericsh" wrote: I'm using the following function to detect the size of the Y-axis in a chart that is autoscaled by Excel: Public Function MaxChartY() MaxChartY = Worksheets("CTest").ChartObjects.Item(1).Chart.Axe s(xlValue, xlPrimary).MaximumScale End Function Then, in a cell on the "CTest" sheet, I enter: =MaxChartY() When first entered, the formula correctly shows the Y-axis maximum value, but when data for the chart is changed and the Y-axis autoscales accordingly, my MaxChartY formula does not update. How can I get my formula to update automatically when the Y-axis autoscales because of a change in the data feeding the chart? -- ericsh ------------------------------------------------------------------------ ericsh's Profile: http://www.excelforum.com/member.php...o&userid=26149 View this thread: http://www.excelforum.com/showthread...hreadid=568574 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF statement based on need to recalculate | Excel Worksheet Functions | |||
Recalculate based on 3 of 4 variables. | Excel Discussion (Misc queries) | |||
Excel to recalculate a worksheet based on a time interval | Excel Worksheet Functions | |||
Recalculate function | Excel Programming | |||
My VBA Function Won't Recalculate | Excel Programming |