PDA

View Full Version : Solved: Excel 2003: Disable screen refresh during intensive chart updating



Artifical20
05-08-2009, 01:23 AM
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.

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


P.S. This is my first post here, I'm sorry if something is done wrong.
Edit: Worksheet attached
Best regards,
Arnis

hardlife
05-08-2009, 01:42 AM
Arnis, me can hardly imagine, how does this all works and what does this do,
may be me will not be able to help You, but if You can post sample data me will be happy to learn something new, if me will be able to help, me will try to do my best.

Happy and sunny day
Pavel

Artifical20
05-08-2009, 01:53 AM
hardlife,

thanks for replying. My worksheet currnetly is like a sample file, no real data is incorporated yet, I'm just testing if this technique is viable. therefore I have attached the file to my first post.

After opening the file, press small button with pen symbol on it, then press CTRL and move mouse over right-bottom chart chartarea. You will notice blinking.

Best regards,
Arnis

hardlife
05-08-2009, 03:53 AM
Hi Arnis, may be this is not solution but look at this:

CEventChart

me understood that chart want to redraw every movement,
so You must find some compromise, my choise is to wait for specified time,

Application.Wait Now + TimeValue("00:00:02")

may be this is not the best solution.

HAPPY AND SUNNY DAY

Pavel Humenuk



Private Sub EvtChart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)

Dim i As Integer
Dim strChName As String
Dim strOldChart As String
Dim intMainNum As Integer
Dim ActChart As Object
Set ActChart = ActiveChart


If Left(ActiveChart.Parent.Name, 6) = "chMain" Then
If ElementID = 0 Or ElementID = 12 Or ElementID = 3 Or ElementID = 24 Then
intMainNum = CInt(Right(ActChart.Name, 1))
strChName = "chSub" & intMainNum & "_" & Arg1
'MsgBox intMainNum & " " & strChName & " " & Arg1 & " " & ElementID
strOldChart = CStr(Sheets("Main").Cells(101, 1).Value)

If ElementID = 24 Then
Sheets("Main").Cells(1, 1).Select
GoTo Finish2
End If
'MsgBox ElementID & " " & Arg1 & " " & Arg2 & " " & ActiveChart.Name
If ElementID = 0 Or ElementID = 12 Or ElementID = 3 Then
Application.ScreenUpdating = False
Sheets("Main").ChartObjects(strOldChart).Visible = False
Sheets("Main").ChartObjects(strChName).Visible = True
Sheets("Main").Cells(101, 1).Value = CStr(Sheets("Main").ChartObjects(strChName).Name)
Sheets("Main").Cells(1, 1).Select
GoTo Finish2
End If
Finish:
End If
End If


If Left(ActiveChart.Parent.Name, 5) = "chSub" Then
If ElementID = 0 Or ElementID = 12 Or ElementID = 3 Or ElementID = 24 Then
Dim strRngName As String
Dim strRngLabelName As String

If ElementID = 24 Then
GoTo Finish2
End If
Application.ScreenUpdating = False
If ElementID = 0 Or ElementID = 12 Or ElementID = 3 Then
strRngName = "rng_m" & Left(Right(ActiveChart.Parent.Name, 3), 1) & "_ser" & Right(ActiveChart.Parent.Name, 1) & "_" & Arg1 & "_data"
strRngLabelName = Left(strRngName, 14) & "tcell"
Sheets("Main").Cells(110, 1).Value = CStr(Range(strRngLabelName).Address(ReferenceStyle:=xlR1C1))
Sheets("Main").Cells(1, 1).Select
End If

Finish2:
End If
End If
Application.ScreenUpdating = True

End Sub


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

'Application.ScreenUpdating = False
Application.Wait Now + TimeValue("00:00:02")
'Application.ScreenUpdating = True

End Sub

Artifical20
05-08-2009, 04:11 AM
Hi Arnis, may be this is not solution but look at this:

CEventChart

me understood that chart want to redraw every movement,
so You must find some compromise, my choise is to wait for specified time,

Application.Wait Now + TimeValue("00:00:02")


Thanks for idea!

I have been thinking about this - wait for 1-2 seconds - not after every time MouseMove runs, but collect all information from MouseMove event into array, and update sourcedata from this array every 1-2 seconds. This is a bit complicated to make, but will be the best choice, if it isn't possible to create real-time updates without blinking.
For the moment, however, I want to find out exactly what causes charts to blink - if it is update in source data, then probably nothing much can be done. But it may be due to other things - source data being in another worksheet, for example. I am trying to research them at the moment.

Best regards,
Arnis

Artifical20
05-11-2009, 01:19 AM
SOLVED

The problem was in "Screenupdating = true/false" lines. "Screenupdating = true" caused whole screen to refresh every time. Previously I disabled only "screenupdating = false" line, and left "screenupdating = true" active. Now, after disabling both screenupdating = true/false lines, only the 3rd chart (for input) is blinking,everything else is still (but updating real-time).

Credit to rorya at MrExcel for pointing this out.

Thanks to everyone who provided help!

Best regards,
Arnis

Bob Phillips
05-11-2009, 01:49 AM
Credit to rorya at MrExcel for pointing this out.

But absolutely NO credit to you for failing to point out that you had cross-posted this elsewhere.

Artifical20
05-11-2009, 03:16 AM
But absolutely NO credit to you for failing to point out that you had cross=posted this elsewhere.

I am sorry for this, I wanted to add link for cross-post, however I couldn't add link before my postcount reached 5. Looking back at this, I understand that I had to mention the cross-post even if I couldn't provide the linkt to it. This is my 5th post, I can no more edit my OP, thus I am providing link here.

Cross-posted: http://www.mrexcel.com/forum/showthread.php?t=388995

Best regards,
Arnis

Aussiebear
11-20-2014, 08:19 PM
I am sorry for this, I wanted to add link for cross-post, however I couldn't add link before my postcount reached 5. Looking back at this, I understand that I had to mention the cross-post even if I couldn't provide the linkt to it.

Best to be upfront with everyone from the start. That way you don't risk upsetting anyone no matter which forum you post in.