Delete sheets without confirmation prompts using VBA in Microsoft Excel

by  About
       

Turn off warning messages using VBA in Microsoft Excel

You can run the macros either from the Visual Basic Editor by placing your cursor in the macro and pressing the F5 key, or from Excel by opening the Macros dialog box (ALT+F8) choosing the macro to run and clicking Run. It is best to run these macros from Visual Basic Editor by using Debug > Step Into (by pressing F8) so you can watch them as they work.

Instruction

If Developer Tab is not in the Ribbon..

  • Open Excel.
  • Go to VBA Editor (press Alt + F11)
  • Go to Immediate Window. ( Ctrl + G)
  • Write below Code.
    • Application.ShowDevTools = True

How to Insert VBA code in Excel

  • Go to Developer Tab > Code Group > Visual Basic
  • Click Insert > Module.
  • Will open a Blank Module for you.
  • Write / Paste provided code in that Module

Untitled-1

How to Run VBA code in Excel

  • Select anywhere in between the Code, Sub… End Sub
  • Click Run & Run Sub or F5Untitled-1

    Sometimes the application displays a message box asking if the user wants to continue,

    if he/she wants to save a file before closing, or want some other verification from the user, or you want to delete a existing sheet with data.

     

     

    These messages can be turned off by using this command in a macro. All these type of messages are known as DisplayAlert Message. By using Application.DisplayAlert turned off or on, you can avoid these type messages to show in the screen.

     

    If you want to delete sheets from a workbook without the user being prompted for any

    confirmations you can use this macro:

     

    Delete Sheet from a Workbook

    Code

    Sub DeleteSheet(strSheetName As String)
    ' deletes a sheet named strSheetName in the active workbook
    Application.DisplayAlerts = False
    Sheets(strSheetName).Delete
    Application.DisplayAlerts = True
    End Sub

     

    Decode

        Application.DisplayAlerts = False

          …..

        Application.DisplayAlerts = True

    In between these two line, excel overlook all alert messages to come. So, now if you delete the sheet, and sheet has some data, excel will not ask you for confirmation.

    Png1



One thought on “Delete sheets without confirmation prompts using VBA in Microsoft Excel

  1. “If anyone uses ASAP utilities (personally I can’t live without it) there is a list all range names function that lists all range names on a new worksheet.

    Even after deleting all range names in INSERT/DEFINE/DELETE menu it still lists them all. Is this a problem with ASAP or are they still really there?”

Leave a Reply

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


4 − two =

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>