Excel 2003: Disable screen refresh during intensive chart updating

[Artifical20]

Good afternoon,

Problem:
I have a problem with blinking charts in a worksheet. User is manually udating charts (often few times per second) and this causes blinking. My guess is that when values on a worksheet are changed, charts update, thus causing blinking. I want to know if it is possible to avoid blinking.

Worksheet:
Worksheet is aimed at evaluating different future scenarios. At all times I have three column charts visible on the worksheet:
1) Main chart
2) Factors chart
3) Input chart

Main chart - Stacked column chart, contains all relevant series for some time period
Factors chart - one stacked column chart for every series in main chart, divides it in smaller factors (series), is made visible when user clicks on a relevant series on a main chart (other factor charts under the active one are hidden when this happens)
Input chart: Column chart with single series. When user slicks on a series in a factor chart, input chart takes the same range for its values (dynamic named range). User activates plot area, holds CTRL, and by moving mouse over the chart area changes the values of the series (user "draws" the expected future values of a factor). Changes are immedaitely displayed in all three visible charts.

Technique used:
Windows XP SP3, Excel 2003.
All the worksheets in the workbook are neither locked nor protected. There are no forzen panes.
Charts are clickable because I use CEventChart class module developed by John Peltier. First two charts are made clickable by EvtChart_Select event, Input chart tracks mouse movement using EvtChart_MouseMove event. XY coordinates of mouse pointer (when CTRL is pressed over chart area) are transformed into X and Y axis values. Appropriate column takes the Y value "drawn" by user with mouse (value immedately replaces old value in the source data of input chart, in another worksheet of the same workbook). This causes all charts to update few times per second (MouseMove event updates at this rate), thus blinking.
I understand that I can store all values on worksheet/in array, and update charts every X seconds/after user releases CTRL, however user wants to see changes in all charts real-time.
I use screenupdating = true/false, have tried placing them in various places. I do not use select/activate in Input chart code.
Worksheet with charts on them uses change/activate/deactivate events. Change event tracks changes in three cells, none of them is changed during input. I tried disabling all these events, blinking contionued as before.
Code for the MouseMove event used to track input is displayed below, it should be the only code which runs while user is "drawing" data.
Excel file could not be attached to this message, however if it is needed to provide help on this issue, it is available in the location where this question is cross-posted (see below). After opening press button with pen image, and move mouse over bottom-right chart chartarea while holding CTRL, all charts will blink. Thanks a lot, I will appreciate any help on this problem.

 [LEFT]Private Sub EvtChart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long) [/LEFT]

 
 
[LEFT]  'check if CTRL is pressed

 If Shift = 2 Then 
[LEFT]      'check if Mouse is moved over the Input chart
     If Left(ActiveChart.Parent.Name, 7) = "chInput" Then 
          'turn off screen updating
         Application.ScreenUpdating = False 
          'declare variables
         Dim PlotArea_InsideLeft As Double 
         Dim PlotArea_InsideTop As Double 
         Dim PlotArea_InsideWidth As Double 
         Dim PlotArea_InsideHeight As Double 
         Dim AxisCategory_MinimumScale As Double 
         Dim AxisCategory_MaximumScale As Double 
         Dim AxisCategory_Reverse As Boolean 
         Dim AxisValue_MinimumScale As Double 
         Dim AxisValue_MaximumScale As Double 
         Dim AxisValue_Reverse As Boolean 
         Dim datatemp As Double 
         Dim Xcoordinate As Double 
         Dim Ycoordinate As Double 
         Dim X1 As Double 
         Dim Y1 As Double 
         Dim PlotArea As Object 
         Set PlotArea = ActiveChart.PlotArea 
         Dim ChartArea As Object 
         Set ChartArea = ActiveChart.ChartArea 
         Dim Axes As Object 
         Set Axes = ActiveChart.Axes 
         Dim dblMinDate As Double 
         Dim dblMaxDate As Double 
         Dim intPointNum As Integer 
         Dim yyy As Double 
          'account for zoom settings
         X1 = x * 75 / ActiveWindow.Zoom 
         Y1 = y * 75 / ActiveWindow.Zoom [/LEFT]
[/LEFT]

 
 
 
 
[LEFT]          'plotarea settings

         PlotArea_InsideLeft = PlotArea.InsideLeft + ChartArea.Left 
[LEFT]         PlotArea_InsideTop = PlotArea.InsideTop + ChartArea.Top 
         PlotArea_InsideWidth = PlotArea.InsideWidth 
         PlotArea_InsideHeight = PlotArea.InsideHeight [/LEFT]
[/LEFT]

 
 
 
 
[LEFT]          'determine X axis scale (how many columns)

         With Axes(xlCategory) 
[LEFT]             AxisCategory_MinimumScale = Range("rngDateStart").Value 
             AxisCategory_MaximumScale = Range("rngDateFinish").Value 
             AxisCategory_Reverse = .ReversePlotOrder 
         End With 
          'Y axis scale
         With Axes(xlValue) 
             AxisValue_MinimumScale = .MinimumScale 
             AxisValue_MaximumScale = .MaximumScale 
             AxisValue_Reverse = .ReversePlotOrder 
         End With 
          'transfer XY coordinates to X/Y axis values
         datatemp = (X1 - PlotArea_InsideLeft) / PlotArea_InsideWidth * (AxisCategory_MaximumScale - AxisCategory_MinimumScale) 
         Xcoordinate = IIf(AxisCategory_Reverse, AxisCategory_MaximumScale - datatemp, datatemp + AxisCategory_MinimumScale) 
         datatemp = (Y1 - PlotArea_InsideTop) / PlotArea_InsideHeight * (AxisValue_MaximumScale - AxisValue_MinimumScale) 
         Ycoordinate = IIf(AxisValue_Reverse, datatemp + AxisValue_MinimumScale, AxisValue_MaximumScale - datatemp) 
          'round X coordinate to determine which column has to be changed, store in yyy
         yyy = Round(Xcoordinate, 0) - Range("rngDateStart").Value + 1 
          'replace value of appropriate column in source data (another worksheet)
         Range("rngDynamic").Cells(yyy, 1).Value = Ycoordinate 
          'turn on screenupdating
         Application.ScreenUpdating = True 
     End If 
 End If 
End Sub [/LEFT]
[/LEFT]

P.S. This is my first post here, I'm sorry if something is done wrong.​

P.S.2 Cross-posted: [URL]http://www.vbaexpress.com/forum/showthread.php?t=26607[/URL]

Best regards,

Arnis<!-- / message --><!-- attachments -->​

 

[Artifical20]

Update: I started experimenting with delayed updates (ontime method), however real-time without blinking would be much better. Main question is - what is causing blinks (everything on the worksheet is blinking, even buttons). If they are caused by charts updating, then probably there is nothing much I can do, if the reason is different - maybe there is a solution for this.

Thanks to everybody who are looking into this issue.

Best regards,
Arnis

 

[RoryA]

Charts are going to blink if you are updating their values in a mousemove event I think (that's a lot of events). The rest may be down to you using Screenupdating = True in the mouse move - that forces a redraw of the screen for each movement of the mouse.

 

[Artifical20]

Charts are going to blink if you are updating their values in a mousemove event I think (that's a lot of events). The rest may be down to you using Screenupdating = True in the mouse move - that forces a redraw of the screen for each movement of the mouse.

Thanks a lot for the answer!

As for Screenupdating = True - As far as I know, there is no possibility for me NOT to use it. If I disable this line, Excel will set Screenupdating to true anyways, when macro is finished. Am I wrong?

Best regards,
Arnis

 

[RoryA]

No, you're not - it will be turned back on. But I believe that by turning it off and on again, you would force a refresh of the whole screen which might not have been required otherwise. Worth a test at any rate!

 

[Artifical20]

But I believe that by turning it off and on again, you would force a refresh of the whole screen which might not have been required otherwise. Worth a test at any rate!

Thanks! This proved to be 100% correct. After disabling both screenupdating = true/false lines, only the 3rd chart (for input) is blinking,everything else is still (but updating real-time).

Previously I disabled only "screenupdating = false" line, and left "screenupdating = true" active. This really forced whole screen to refresh.

SOLVED (Unfortunately I cannot find how to mark thread as solved here in MrExcel, I hope moderators can do it if neccesarry).

Best regards,
Arnis