Solved: Excel 2003: Disable screen refresh during intensive chart updating
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:
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. If the worksheet is neccesarry, please let me know. Thanks a lot, I will appreciate any help on this problem.
[vba]Private Sub EvtChart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
'check if CTRL is pressed
If Shift = 2 Then
'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
'plotarea settings
PlotArea_InsideLeft = PlotArea.InsideLeft + ChartArea.Left
PlotArea_InsideTop = PlotArea.InsideTop + ChartArea.Top
PlotArea_InsideWidth = PlotArea.InsideWidth
PlotArea_InsideHeight = PlotArea.InsideHeight
'determine X axis scale (how many columns)
With Axes(xlCategory)
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[/vba]
P.S. This is my first post here, I'm sorry if something is done wrong.
Edit: Worksheet attached
Best regards,
Arnis
Reply With Quote
Originally Posted by hardlife
