Delete a sheet on closing the workbook

[palaeontology]

Hi all,

I've written the following code to delete the sheet called ... Entry (2) .... when exiting the workbook ?

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, _Cancel As Boolean)
For Each sht In Sheets
If sht.Name = "Entry (2)" Then
sht.Delete
End If
Next sht
Workbook.Save
End Sub

However, it doesn't seem to be deleting the sheet ... when I re-open the workbook, the sheet is still there.

Can anyone see what I've done incorrectly ?

Kind regards,

Chris

 

[Fluff]

Try

Private Sub Workbook_BeforeClose(Cancel As Boolean)
For Each Sht In Sheets
If Sht.Name = "Entry (2)" Then
Sht.Delete
End If
Next Sht
ThisWorkbook.Save

End Sub

 

[ONEMINUTETUTORIAL]

Sub sbDeleteASheet()'Stopping Application Alerts
Application.DisplayAlerts=FALSE
Sheet1.Delete
'OR You can mention the Sheet name
Sheets("Sheet2").Delete
'Enabling Application alerts once we are done with our task
Application.DisplayAlerts=TRUE
End Sub

Hi friend ,

Change the sheet name.

Regards

 

[Logit]

Option Explicit


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Dim Workbook As Workbook
Application.DisplayAlerts = False


    For Each sht In Sheets
        If sht.Name = "Entry (2)" Then
            sht.Delete
        End If
    Next sht
    
ThisWorkbook.Save
Application.DisplayAlerts = True
End Sub

 

[xenou]

1) Check spelling.
2) put it in the BeforeClose Event in the this workbook module. I mention this because it's not clear what event you are using. App_WorkbookBeforeClose makes it appear you have created a custom workbook class but not at all sure what you are doing so you should make that clear.

 

[xenou]

P.S. You better do some error handling to avoid the code crashing if the sheet doesn't exist or is already deleted.

 

[palaeontology]

Hi all,

I ended up going with the following code ...

Option Explicit


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Dim Workbook As Workbook
Application.DisplayAlerts = False


    For Each sht In Sheets
        If sht.Name = "Entry (2)" Then
            sht.Delete
        End If
    Next sht
    
ThisWorkbook.Save
Application.DisplayAlerts = True
End Sub

However, I changed "Entry (2)" to "Entry (#)" as there might be more than one copy of the Entry worksheet that needs to be deleted.

Unfortunately, it doesn't seem to be deleting any of the copies of the Entry sheet.

Also, Upon closing the workbook, how would I have the code changed to force the Entry sheet to be the one that is displayed next time the user opens the workbook ?

Kind regards,

Chris

 

[Logit]

.

Paste in ThisWorkbook :

Option Explicit


Private Sub Workbook_Open()
    Sheets("Entry").Select
End Sub

 

[Fluff]

To delete the sheets try

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.DisplayAlerts = False


    For Each sht In Sheets
        If sht.Name Like "Entry (*)" Then
            sht.Delete
        End If
    Next sht
    
ThisWorkbook.Save
Application.DisplayAlerts = True
End Sub

 

[xenou]

^^ looks good to me. I shouldn't have said that error handling would be needed to prevent crashes since this is in an If test.

I suppose another option would be to put this in a before save event? Then you could close without saving if you preferred to not save the workbook at all.