Apr 07 2020 02:36 PM
I have a excel chart which may contain 0-255 series. Since the chart legend can't hold all the names, I would like a formula that would just put the total number of series in the object in an excel cell on the same worksheet.
I put this macro in to do this, but it does not auto-update when I delete series. Is there a way to put the macro code into the cell formula directly?
Or is there a way to put update code into a chart change event?
Function DisplaySeriesCount(strSheetName As String, strChartName As String) As Integer
Dim iAnswer As Integer
iAnswer = Sheets(strSheetName).ChartObjects(strChartName).Chart.SeriesCollection.Count
DisplaySeriesCount = iAnswer
End Function
Aug 25 2020 08:26 PM
Your UDF is a non-volatile type try to recalculate it
ActiveSheet.Calculate
or
Range.Calculate
More info about realculations can be found below:
https://docs.microsoft.com/en-us/office/client-developer/excel/excel-recalculation