Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Excel Chart "flickering" & UserForm "repainting"

  Asked By: Gene    Date: Feb 18    Category: MS Office    Views: 4802
  

I have the solution to the Chart "flickering" and UserForm "repainting" itself.
You can now do the Application.ScreenUpdating=False to optimize any subsequent
code "performance".
Plus, never have to revert back to Application.ScreenUpdating=True
The solution is this:
"Freeze" the current location of the Userform(panel) to prevent it from
"repainting" itself.
The person can initially move the Userform(panel) around before making any
selections within it.
When the Userform(panel) is unloaded, it "automatically" resets the properties
of the screen and the chart.
The VBA coded solution is shown below and also the updated WINZip file (
w/example Excel Chart application );
it having the solution will be uploaded to the files section.


Option Explicit
'-VBA Code and definitions to prevent Chart flickering and Userform "repainting"
itself
'-by freezing the current location of a Userform(panel).
Private Const MF_BYCOMMAND = &H0&
Private Const SC_MOVE = &HF010&
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As
String) As Long
Private Declare Function GetSystemMenu Lib "user32" _
(ByVal hwnd As Long, ByVal bRevert As Long) As Long
Private Declare Function DeleteMenu Lib "user32" _
(ByVal hMenu As Long, ByVal nPosition As Long, ByVal
wFlags As Long) As Long

Sub FreezeForm(ByRef myForm As MSForms.UserForm)
'============================================================
'-Keeping the location of a Userform.
'-The following code would keep the location of a userform;
'-in other words, it prevents users to move a userform.
'============================================================
Dim hwnd As Long
Dim hMenu As Long
Dim ret As Long
Dim sClass As String

'Check the version of Excel
If Int(Val(Application.Version)) > 8 Then
'Excel 2000 and the later version
sClass = "ThunderDFrame"
Else
'Excel 97
sClass = "ThunderXFrame"
End If

hwnd = FindWindow(sClass, myForm.Caption)
hMenu = GetSystemMenu(hwnd, 0&)
ret = DeleteMenu(hMenu, SC_MOVE, MF_BYCOMMAND)
End Sub

Sub ChartTest()
'==================================
'-Freeze Userform(panel) in its
' current position to prevent
'-it from repainting itself.
'-=================================
FreezeForm UserForm1
'======================================
'-The following
'-(Application.ScreenUpdating = False)
'-is to optimize performance of
'-subsequent VBA code being executed.
'=======================================
Application.ScreenUpdating = False
'==============================================
'-Process data from other workbook OR worksheet.
'Sheets("HiddenSheet").Activate
'==============================================


'================================
'-Other extensive VBA Code
'-being executed in this section.
'-Which includes opening&accessing
'-other workbooks and its sheets.
'================================

'-NOW return control back to the user.

'Sheets("TestChart").Activate

End Sub

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Sumitra 2004     Answered On: Feb 18

Here is a much better solution, for fixing simultaneously both the
Excel(main chart) "flickering" & Userform "repainting" situations.
The VBA code shown  below, can be placed into any Userform(panel).

Option Explicit
'=================================
'-Variable indicating whether
'- "cascading" is (True) or (False).
'-This would be placed in the
'-declaration section  of the module.
'=================================
Public frmCASCADE As Boolean

The subsequent procedure( PCascade ), can be placed into any workbook module.
Sub PCascade ( )
Application.ScreenUpdating = False
frmCASCADE = True
End Sub

'===============================================
'-The following examples shows when calling the above
'-procedure(PCascade) to "optimize" performance  and
'- also sets the global variable(frmCASCADE) to (True).
'===============================================

Private Sub SpinButton1_SpinDown()
Call PCascade
'=================================
'-Other VBA code  to be executed.
'=================================
'...
'...VBA Code
'...
'=================================
'-Control is returned back  to the user.
'=================================
End Sub

Private Sub SpinButton2_SpinUp()

Call PCascade
'=================================
'-Other VBA Code to be executed.
'=================================
'...
'...VBA Code
'...
'=================================
'-Control is returned back to the user.
'=================================
End Sub

'==========================================
'-Solution Code is shown below for any Userform.
'==========================================
Private Sub UserForm_Initialize()
frmCASCADE = False
Cascade.StartUpPosition = 0
Cascade.Top = (Application.Height / 8) - (Cascade.Height / 8)
Cascade.Left = (Application.Width - Cascade.Width) / 2
End Sub

Private Sub UserForm_Layout()
'======================================
'-Each time when Userform(panel) is
'-moved/slid around on the screen,
'-this code is reexecuted each time.
'-Therefore, check the global variable
'-(frmCASCADE) is ( True ), which
'-indicates that this Userform(panel)
'-is in cascading mode.
'======================================
If frmCASCADE Then
'================================================
'-Then reset the screen  to prevent  the cascading
'-of this Userform(panel). Also, reset global
'-variable(frmCASCADE) to (False), so next time
'-when this Userform(panel) is moved, this code
'-is not rexecuted again, until the (frmCASCADE)
'-variable is set to (True) again.
'================================================
Application.ScreenUpdating = True
frmCASCADE = False
End If
End Sub

 
Answer #2    Answered By: Betty White     Answered On: Feb 18


To follow naming conventions, some clarifications here:
The global variable ( frmCascade ) would be ( bCascade ) for boolean..
And also the items that were named as Cascade.(something?), should be
Me.(something?)

 
Answer #3    Answered By: Beverly Brooks     Answered On: Feb 18

Here is the actual solution  per above subject.

Private Sub UserForm_Layout()
'================================================
'-Each time when Userform(panel) is moved/slid around
'-on the screen, this code  is reexecuted each time.
'-Therefore, check if Application.ScreenUpdating, is ON
'- or OFF; which indicates that this Userform(panel) is in
'-overlaying imaging mode.
'======================================
If Not Application.ScreenUpdating Then
'================================================
'-Then reset the screen  to prevent  the overlaying
'-of this Userform(panel) to itself.
'================================================
Application.ScreenUpdating = True
End If
End Sub

 
Didn't find what you were looking for? Find more on Excel Chart "flickering" & UserForm "repainting" Or get search suggestion and latest updates.




Tagged: