Custom excel function not updating

' Force the charts to update Set sht = Active Sheet For Each co In sht. I found that if I went back into the "Select Data Source" msg box and unchecked/rechecked the "show data hidden in rows and columns" that the chart refreshes. After that, using radio buttons to select data to be presented on graphs did not update the graphs anymore. It then asked me to change a value and watch the percentage change, then to look at the pie chart and see the update. I looked at the data source for the pie chart, and the range was bizarre. If I closed everything and opened just the file I was having problems with the charts where dynamic like they should be. Be sure you are copying and pasting pictures before you go through all the other motions.... When generating the charts automatically, some charts appear over and cache the text in the sheet. My solution has been to make all charts on the active sheet invisible before the change in data, then make them visible again and call chart refresh for good measure. I change a cell value which contains, for example, the date range to base the dashboard on, and the named ranges and UDFs are forced to calculate. You must send values out of chart cells and after this use code like this Worksheets("sheet1"). The chart axis that were not updating references ranges with hidden columns, similar to others in this chain, and the labels displayed the month and year "MMM-YY" of the dynamic data. I tried all solutions listed except for the VBA options as I'd prefer to solve without code.

Screen Updating = False For I = 1 To Active Workbook. What I found is that if I double-clicked the line (appearing with bad data), it showed me that it was on the SECONDARY axis for some reason. Charts are not "feeling" changes with direct inserting values to sorurce cells with macro. Value I was having a similar problem today with a 2010 file with a large number of formulas and several database connections.

Working on the sheet I would do a quick Ctrl-X, Ctrl-V on a piece of the chart (or the whole thing) to force the chart to update. Also, I'm not really sure why this works; I suppose when Wrap Text changes one property of the data range it makes the graph update, although I have no documentation on this. So I post the solution here if it interest someone. The refresh may not be necessary - more testing needed. Anyway, let's cut to the solution: I didn't want to visibly change the chart title or some other aspect of it, and anyway I noticed this wasn't updating my charts consistently. Screen Updating = True End Sub So, the sheet named Charts Data, which references the custom function cell of Sheet 1, will do a recalculation AFTER the Compute Score() function has updated the cell of Sheet 1, since Worksheet_Calculate() fires after the Compute Score() recalc. Select 'Forwhatever reason a Select statement was needed . What I eventually had to do was to break out the querydef creation and the property setting into separate subs.

This is the only thing I've found to consistently update a chart. This is an absurd bug that is severely hampering my work with Excel. Programmatically, I added this after generating charts : Simply redefined the Source Data range. Screen Updating = True End Sub Sub Repaste Charts() Dim Str Temp As String Dim Int Temp Top As Integer Dim Int Temp Left As Integer Set sht = Active Sheet For Each co In sht. Activate 'Grab current position on worksheet Int Temp Top = Active Chart. Dim chrt As Chart Dim chrt Vis As Xl Sheet Visibility Dim sht As Worksheet Dim b Chart Visible() As Boolean Dim i Count As Long Dim co As Chart Object On Error Resume Next Set chrt = Active Chart If Not chrt Is Nothing Then chrt Vis = chrt. Visible = xl Sheet Hidden End If Set sht = Active Sheet If Not sht Is Nothing Then Re Dim b Chart Visible(1 To sht. Sometimes the first time I triggered the calculation the pies would update, but with subsequent calculations the pies would not. This additional round of calculation of the chart data causes the chart to update now, rather than later or not at all. Additionally, I put a I had a similar problem - Charts didn't appear to update.

In this massive article, I am going to list some useful Excel macro examples that I need often and keep stashed away in my private vault.

I will keep updating this tutorial with more macro examples.

Leave a Reply