Disappearing Graphs

[Paul E]

This is a modified repost. It did not get any play once it was responded
to...but that solution didn't solve the problem.

I understand Dynamic Range Names would solve this, but my program must run
on hundreds of legacy spreadsheets where they were not used. Since the users
have done major modification, I'm stuck solving this problem w/ code. Here's
the original message:

I have a sheet in a Workbook that holds about 21 charts. When I add data to
the workbook, I need to go into every chart and add rows to each data series
as well as the Horizontal Axis. Since this is tedious, I wrote a Macro to do
this. The Macro works great...but when I run it, any chart that is off-screen
disappears. It still exists, but the only way I can make it visible again is
to save the workbook, close it, and reopen it. I have tried to refresh the
graphs as I update them, but that hasn't fixed it.

The only fix that I have found that works is to temporarily set the Window
zoom to 10% so all the graphs are visible, update the chart ranges, then
reset the Window to the value it was when the Macro ran.

Any idea why the off-screen charts are disappearing? Any suggestions on
eliminating this issue? I'd rather run the Macro w/ ScreenUpdating off so it
will run a bit faster w/out the user seeing the zoom resets, but that defeats
the purpose.

If necessary, I can post the code.

Thanks,
Paul

 

[joel]

From you description the graphs are not off screen but just not gettin
refreshed. If the graphs were really off-screeen then the problem woul
also occur when you close and open the workbooks.

Have you tried to manually perform a recalculte (F9). If you chang
worksheets and then come back to the the 1st worksheet does the chart
get updated? Are there any other workbooks opened? Can you post th
code so I can see where the ScreenUpdaing instructions are located

 

[Paul E]

I've tried the F9 and switching to other sheets, but the graphs do not
re-appear.

I have 3 "printed" pages of graphs. W/ my zoom set at 50% I can see a
portion of the first page on my monitor. After running the script a few
times, w/out doing any scrolling, the graphs that did not have any portion
showing on my monitor appear to have been deleted, or "sent to back" against
the sheet. There is no sign of them.

I have even tried toggling between Page Break Preview and Normal views to
get them to refresh. No luck.

Here's the code:

Sub UpdateGraphRows()
'
' UpdateGraphRows Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Dim oChart As ChartObject
Dim mySrs As Series
Dim NewRow As String
Dim OldFormula As String
Dim FormulaParts() As String
Dim OldRow As String
Dim oSheet As Worksheet
Dim ReplaceValueFound As Boolean
Dim OriginalZoom As Variant
Dim FORCED_ZOOM As Variant

FORCED_ZOOM = 10

'Application.ScreenUpdating = False
NewRow = InputBox("Please enter new ""final"" row number", "New Row
Number")
If NewRow = "" Then
MsgBox "You must enter a new end row.", vbOKOnly, "No Row Provided"
Exit Sub
Else
For Each oSheet In ActiveWorkbook.Worksheets
If oSheet.ChartObjects.Count > 0 Then
oSheet.Activate
OriginalZoom = Windows(1).Zoom
Windows(1).Zoom = FORCED_ZOOM
For Each oChart In oSheet.ChartObjects
For Each mySrs In oChart.Chart.SeriesCollection
If Not ReplaceValueFound Then
OldFormula = mySrs.Formula
FormulaParts = Split(OldFormula, ",")
OldRow = RowToChange(FormulaParts(2))
ReplaceValueFound = True
End If
mySrs.Formula =
WorksheetFunction.Substitute(mySrs.Formula, OldRow, NewRow)
Next
oChart.Chart.Refresh
Next
Windows(1).Zoom = OriginalZoom
End If
Next
End If
'Application.ScreenUpdating = True
MsgBox "Done!", vbOKOnly, "Update Completed"
End Sub
Private Function RowToChange(ByVal SourceFormula As String) As String
'Runs once to figure out old row number
Dim CurrentCharacter As String
Dim ReturnValue As String
Dim ReverseCounter As Integer

For ReverseCounter = Len(SourceFormula) To 1 Step -1
CurrentCharacter = Mid(SourceFormula, ReverseCounter, 1)
If IsNumeric(CurrentCharacter) Then
ReturnValue = CurrentCharacter & ReturnValue
Else
Exit For
End If
Next ReverseCounter
RowToChange = ReturnValue
End Function

From you description the graphs are not off screen but just not getting
refreshed. If the graphs were really off-screeen then the problem would
also occur when you close and open the workbooks.

Have you tried to manually perform a recalculte (F9). If you change
worksheets and then come back to the the 1st worksheet does the charts
get updated? Are there any other workbooks opened? Can you post the
code so I can see where the ScreenUpdaing instructions are located.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=165537

Microsoft Office Help

.

 

[Paul E]

BTW...the zoom changes to 10% before processing and back to the original zoom
after processing is the workaround that makes everything OK. As long as all
the charts are on-screen when they are updating, they don't disappear.

 

[joel]

I don't understand why you need to perform a zoom. The macro shoul
work without zooming. I'm also not sure what excel would do when y
uhave screen updating set to false when you try to zoom. The window ma
lockup. Do you have the problem if you eliminate the ZOO
instructions?

Another possibility is you may be getting an error in the code and th
macro may be exiting before you get to the OriginalZoom or when you se
the ScreenUpdating = True.

Check the Error setting in the VBA menu

Tools - OPtions - General - Error Trapping

I would set it to Stop on All Errors and try running the code again

 

[Paul E]

Joel,

Before adding the zoom logic I would run this w/ the updating set to false.
When I run it w/ the zoom logic, I have that commented out. So, no errors.
Also, It runs through to the end w/ the final message box and all the values
are updated. In fact, after I save and bring up the sheet again, the graphs
are all updated w/ the right new row.

W/out the zoom, I get the disappearing. W/ the zoom, all runs great and none
of the charts disappear.