Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Other than your code not working, one of the most annoying problems that can occur when you run a macro is to have the screen flash several times. This flashing happens when your macro switches between worksheets several times. You might not be able to rewrite your code to avoid the switches, but you can add two lines of code to stop the flashing. So I'm going to open my sample file. It's called NoFlash. I'll double-click it. The workbook does contain macros, but I'm expecting them, so I will click Enable macros. I have also gone to the Excel Preferences dialog and displayed the Developer tab of the Ribbon.
So I'll click that. To run my macro, just to show you how it works and how the flashing occurs, I will click Macros to open the macro dialog, select the macro I want to run, it's called ScreenFlash, and click Run. So this macro switches back and forth between screens several times, and when it's done, it displays the word 'Done!' That type of flashing can be very annoying when it happens at high speed, but there is a little bit of code that you can add to stop it. First, just to make sure I remember, I will delete 'Done!,' so we can tell when the macro has completed running when we run it again.
Now, I will click the Macros button. In the Macro dialog, I will select ScreenFlash, and click Edit. So here's my macro code. What you need to do is add a line of code at the top that reads Application.ScreenUpdating = False. I just pressed Tab when I was done typing in, so that Excel would accept the value. What this line of code does is it tells the Excel application that it should turn off ScreenUpdating until either we get to the end of the macro code, or it sees the ScreenUpdating value set to True.
Just for completeness, I will copy the statement, pressing Command+C, so it's on the clipboard. Now, I will past it above this last line, which displays the value Done! in cell B8. So I'll press Command+V to paste it. Press Return twice, and now the code is in the macro. So ScreenUpdating is turned off here, and it is turned on back down here when I change this value to True. So now I'll press Command+S to save my work and click the View Microsoft Excel button to go back to Excel.
Now when I run the macro, click macros, ScreenFlash, and click Run, Excel is running the macro. You can tell that because the controls in the macro dialog are grayed out. When the macro is done, those controls return and are available. You can see that even though we didn't see the ScreenFlash that we saw before - the orange and the green - we did see the ScreenUpdate to tell us that the macro was done. If you or a colleague record a macro that results in some unsightly screen flash, adding these two lines of code to the top and bottom of the macro will help you prevent that from happening.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 98025 Viewers
80 Video lessons · 141284 Viewers
59 Video lessons · 59633 Viewers
52 Video lessons · 72889 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.