How to deactivate Alerts in your Macro code ?

This is for people who write the automation macros in VBA code to execute multiple tasks. We will talk here about two examples where you will need to deactivate the alerts.

Example 1: We have created a web crawler macro, which extracts or downloads data from website. Here every time you refresh or land on the website i will show some pop-up messages or alerts or shows different screens through which the macro is executing the tasks.

Example 2: we have a macro which is running on excel application. which opens different worksheets to process the data. During the process we can see multiple sheets activated and the programmed tasks running on screen.

Let’s say you don’t want to see all what is happening on screen or in case you are creating the macro for some end user / customer and that user doesn’t want to know what is happening at the background, he just needs output. In this case we can just use 2 lines of code, which will help use to disable the Alerts and any Screen updates, till the macro completes all it’s tasks.

I have written below sample code, which can be used to check the working of the disable alerts and disable screen updating functions. I am using the example to 2 from above (running macro in Excel Workbook) for ease of understanding. This code starts writing 1 to 2000 by activating each sheet in the workbook using the FOR LOOP code.

Below are the only two lines of code, which work to disable Display Alerts and Screen updating.

Application.DisplayAlerts = False
Application.ScreenUpdating = False

To test the below code follow below steps:

1. Open new excel workbook
2. Pres Alt + F11, to open the VBA window
3. Copy and past the below code from start to end
4. Run the code. You will observe the screen changing from Sheet 1 activation to Sheet 3 and the numbers written from 1 to 2000 on each screen
5. Now to test the display alerts and screen updating remove the below commented code and run again.
   code with comments
   ‘Application.DisplayAlerts = False
   ‘Application.ScreenUpdating = FalseCode without comments
   Application.DisplayAlerts = False
   Application.ScreenUpdating = False
6. Now you will not see any movements on the screen. Instead you will just see the final message box saying Your macro is completed !

–Code start

Sub deactivateAlerts()

‘ remove the ‘ (comment) to activate below code
‘Application.DisplayAlerts = False
‘Application.ScreenUpdating = False

Sheets(“Sheet1”).Select
Range(“A1”).Select
For i = 1 To 2000
ActiveCell.Value = i
ActiveCell.Offset(1, 0).Select
Next i

Sheets(“Sheet2”).Select
Range(“A1”).Select
For i = 1 To 2000
ActiveCell.Value = i
ActiveCell.Offset(1, 0).Select
Next i

Sheets(“Sheet3”).Select
Range(“A1”).Select
For i = 1 To 2000
ActiveCell.Value = i
ActiveCell.Offset(1, 0).Select
Next i

‘Please add the below code before ending the macro. Else it will keep the alerts and screen updating disabled for all other codes too.
Application.DisplayAlerts = True
Application.ScreenUpdating = True

MsgBox “Your macro is completed !”

End Sub

–Code end

Thank you for reading. Happy learning !

Please write comments, if you like

Please write comments if you like my posts and also write if you want me to post solutions on any problems which you face on excel.