CODEWITHSUNDEEP
vbaexcel
ClaireLandis
ClaireLandis

Reputation: 355

Delete a Worksheet upon Exit (if it exists) VBA

I am attempting to delete a worksheet from this Excel file upon exit (if it exists). The code I have tells it to automatically delete the sheet and say "yes, delete" in the popup box, but it is not running for some reason. 

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
' This procedure will delete sheet upon exit and select "Yes, Delete" in the 
'  pop-up box
'    
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name = "Temp" Then
        Application.DisplayAlerts = False
        Worksheets("Temp").Delete
        Application.DisplayAlerts = True
    End If
Next
End Sub

Upvotes: 0

Views: 2664

Answers (2)

DisplayName
DisplayName

Reputation: 13386

I think you should code

Private Sub Workbook_BeforeClose(Cancel As Boolean)    
    Application.DisplayAlerts = False
    On Error Resume Next ' this will prevent subsequent line from stoping code should there be no "Temp" sheet 
    Sheets("Temp").Delete
    Application.DisplayAlerts = True
    Me.Save ' be sure you save the workbook before closing it
End Sub

Upvotes: 1

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

You may try something like this...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
Application.DisplayAlerts = False
On Error Resume Next
Set ws = Sheets("Temp")
If Not ws Is Nothing Then
    ws.Delete
    ThisWorkbook.Save
End If
Application.DisplayAlerts = True
End Sub

Upvotes: 1

Related Questions