Turn off warning messages using VBA in Microsoft Excel 2010

 

In this article we will learn how to turn off warning message using VBA in Microsoft Excel 2010.

When we run vba code / macros in our excel files, we see a lot of screen flickering as the actions are being performed on the file. Also, we get popup messages asking to accept the “Save” or “Delete” as per the code. We can disable these so that the code can run from start to end without disturbing the user.  One such alert is shown below -

 

img1

You can turn off these notifications while the code is running and then turn them on once the code is done.

To prevent the screen from flickering, we enter

Application.ScreenUpdating = False at the beginning of the code and Application.ScreenUpdating = True at the end of the code.

Similarly, to prevent the alerts from appearing, we enter

Application.DisplayAlerts = False at the beginning of the code and Application.DisplayAlerts = True at the end.

This is how it looks in the code window –

img2

Here is the code –

Option Explicit

Sub Macro1()
‘Dim statements here

Application.ScreenUpdating = False
Application.DisplayAlerts = False

‘Your code here

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

To copy this code into your file’s code window, press Alt + F11 on your keyboard. On the left hand side you will see Microsoft Excel Objects. Right click and select Insert.  Then click on Module.

Copy the code into the code window on the right.



Example:


7 thoughts on “Turn off warning messages using VBA in Microsoft Excel 2010

  1. I don’t want to display disabl;e or enable Macro while opening excel work sheet. still i want to run the macro. i have written the macro through VB code

    • Excel VBA needs to have macros enabled to run the macro.
      what you are asking the application to do is not going to be viable. A way round this i have found, add a worksheet at the start of the workbook, give the users a message in the middle of the page advising that macros need to be turned on and how to do it.set a macro to run on start up that hides this sheet and shows the rest. on close, hide all the other worksheets and show the message:

      Private Sub Workbook_Open()
      Dim i As Integer

      For i = 1 To ActiveWorkbook.Sheets.count – 1
      If Sheets(i).Visible = False Then
      Sheets(i).Visible = True
      End If
      Next

      Sheet1.Visible = xlSheetVeryHidden

      End Sub

      Private Sub Workbook_BeforeClose(Cancel As Boolean)
      Dim i As Integer
      For i = 1 To ActiveWorkbook.Sheets.count – 1
      If Sheets(i).Visible = True Then
      Sheets(i).Visible = xlSheetVeryHidden
      End If
      Next

      Sheet1.Visible = xlSheetVisible

      End Sub

      When the user opens the workbook, if macros are enabled, it runs the code to show all the sheets, but hides the message.
      If macros are disabled, it shows the message sheet.

      hope this helps

  2. This is not a bug. This is a security feature to prevent running malicious VBA code that is inserted into a file you open.

  3. Very simplified looping options, anyway very good thanks a lot. When we run vba code / macros in our excel files, we see a lot of screen flickering as the actions are being performed on the file.

  4. Very simplified looping options, anyway very good thanks a lot. When we run vba code / macros in our excel files, we see a lot of screen flickering as the actions are being performed on the file.

  5. Pingback: replique cartier tank louis chronographe

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>