Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Remove screen flicker during macro

Posted on 2009-02-17
5
Medium Priority
?
662 Views
Last Modified: 2012-05-06
Does anyone please know how to remove screen flicker while a macro is running in Excel 2007?
0
Comment
Question by:ric1942
  • 2
  • 2
5 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 23667007
Hello ric1942,

Set application.ScreenUpdating = false
and on completion set
application.ScreenUpdating = true

Regards,
Chris
0
 
LVL 13

Expert Comment

by:Brian Withun
ID: 23667156
Every time you set application.screenupdating to true, it redraws.

Try not to turn it off too frequently, or use this


if not Application.ScreenUpdating then Applications.ScreenUpdating = true

Open in new window

0
 

Author Comment

by:ric1942
ID: 23675583
Thank you both for your answers. Can you show me where to place the code please as I keep getting a compilation error. Code attached.
Regards, Ric.

Sub Emerald()
'
' Emerald Macro
' Returns staff due Emerald award
'
' Keyboard Shortcut: Ctrl+e
'
        Range("I1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$M$100").AutoFilter Field:=9, Criteria1:=RGB(0, 0, _
        0), Operator:=xlFilterCellColor
    
    With ActiveSheet.AutoFilter.Filters(9).Criteria1
        .Pattern = xlPatternLinearGradient
        .Gradient.Degree = 90
        .Gradient.ColorStops.Clear
    End With
    With ActiveSheet.AutoFilter.Filters(9).Criteria1.Gradient.ColorStops.Add(0)
        .Color = 16777215
        .TintAndShade = 0
    End With
    With ActiveSheet.AutoFilter.Filters(9).Criteria1.Gradient.ColorStops.Add(1)
        .Color = 11044088
        .TintAndShade = 0
    End With
    End Sub

Open in new window

0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 2000 total points
ID: 23678256
As below, though I am concerned you have the error as there doesn't seem to be any reason for it in the code.

Chris
Sub Emerald()
'
' Emerald Macro
' Returns staff due Emerald award
'
' Keyboard Shortcut: Ctrl+e
'
    application.ScreenUpdating = false
        Range("I1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$M$100").AutoFilter Field:=9, Criteria1:=RGB(0, 0, _
        0), Operator:=xlFilterCellColor
    
    With ActiveSheet.AutoFilter.Filters(9).Criteria1
        .Pattern = xlPatternLinearGradient
        .Gradient.Degree = 90
        .Gradient.ColorStops.Clear
    End With
    With ActiveSheet.AutoFilter.Filters(9).Criteria1.Gradient.ColorStops.Add(0)
        .Color = 16777215
        .TintAndShade = 0
    End With
    With ActiveSheet.AutoFilter.Filters(9).Criteria1.Gradient.ColorStops.Add(1)
        .Color = 11044088
        .TintAndShade = 0
    End With
    application.ScreenUpdating = true
    End Sub

Open in new window

0
 

Author Closing Comment

by:ric1942
ID: 31548140
Fantastic Chris. It works really well. Thank You VERY much. Regards, Ric.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question