Deleting sheets with VBA

Posted by Anna DAly on December 03, 2000 9:19 AM

Hi All,

I have a small problem that should be quite easy to solve! I am trying to delete a sheet from a workbook using VBA code. I have succeeded in doing this with worksheet("sheet1").delete. However, it comes up with the standard dialogue box "are you sure you want to delete this sheet?" I don't want to be asked this question, I just want it to delete the sheet, and keep quiet about it. Any ideas?

kind regards

Anna

Posted by JAF on December 03, 2000 11:15 AM

This is one of those instances where even though you know exactly what you're doing, Excel takes the moral high ground and assumes you're an idiot and that the last thing you'd want to do is to delete a worksheet.

You can however get Excel to shut up and stop nagging you by setting the DisplayAlerts property to False, doing what you want to do and then setting DisplayAlerts back to True again (which is always a good idea, as it doesn't automatically reset and if you leave it turned off Excel won't remind you to (for example) save changes before closing.

The code you need is . . .
Sub DeleteWithoutConfirmation()
Application.DisplayAlerts = False
Sheets("SheetName").Delete
Application.DisplayAlerts = True
End Sub

HTH
JAF

Posted by Anna on December 03, 2000 12:14 PM

Thanks JAF for your help, that little trick will come in really handy with other problems aswell.