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