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

 

Comments

  1. "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."

  2. 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..

  3. "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"

  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 Donard,
    Difficult to give much help without some details, like what version of Excel, and what exactly the code is that causes the error, but from what you posted, a search of MS support provides this answer that fits your question:
    http://support.microsoft.com/default.aspx?scid=kb;en-us;182500
    Alan."

  6. 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??

  7. 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?

  8. "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"

Leave a Reply to George Cancel reply

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

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.