Eliminate screen flashing while running a macro using VBA in in Microsoft Excel

Enter the code line at the begining of the code:

Application.ScreenUpdating = False

Enter the code line at the end of the code to turned back the screen:

Application.ScreenUpdating = True

 

Users are saying about us...

  1. “i use this code alot, all you have to do is replace the SaveName to be the cell.value
    SaveMonth = MonthName((Month(Date) – 1))
    SaveName = “”BNSF “” & SaveMonth & “” Monthly Sales.XLS””
    Application.Dialogs(xlDialogSaveWorkbook).Show Arg1:=SaveName, Arg2:=””1″”
    hope this works for you”

  2. I have discovered that in one of my Macro’s the Application.ScreenUpdating = False line is ignored every time the macro runs – you can “step through” the code and watch this line compile, yet the ScreenUpdating status does not change. Any ideas?

  3. I received a message error ” Class not registered” when opening excel files with macros in my computer what should i do to open the files??

  4. I am trying to suppress a great deal of my macro’s visual activity. Setting ScreenUpdating=False should do the trick, but Excel has a nasty habit of resetting ScreenUpdating at the most inopportune times. There has got to be a better way..

    • I usually set a bookmark for the value, so that my routines do not accidentally reset it.

      Function LaborDateToData(lrData As Long)
      On Error GoTo ErrorHandler

      Dim bUpdateScreen As Boolean

      bUpdateScreen = Application.ScreenUpdating
      Application.ScreenUpdating = False

      ‘CODE…

      EndProc:
      Application.ScreenUpdating = bUpdateScreen
      Exit Function

      ErrorHandler:
      PostMessage Err.Description, Err.Number
      Resume EndProc
      End Function

  5. “Hi Jamie,
    I seem to faintly recall having that problem once before too.
    If I recall correctly, I just worked around it by scattering the command:
    Application.ScreenUpdating = False
    all over my subs so that it was continuously re-invoked.
    Not elegant, but it worked!
    Alan”

  6. The work-around isn’t foolproof – I use the command in every one of my subroutines where this is relevant, and it still keeps being reset; hence, my initial question..

  7. “Hi Marc,
    Can you post the code for one of the subs where it doesn’t work? Is it consistent every time you run the code (withouth step through)?
    Alan.”

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube