Run Time Error 9 on Deleting sheet

[mgCulver]

In trying to delete a sheet, I constantly get run-time error 9. I think the reason is because the code called to delete the sheet originates from within a button on the sheet; kinda-sorta like suicide prevention.

To Try to avoid this, I placed code in the activate event of another sheet and created a separate module with code to delete the sheet. However, this still generates the same error.

Any suggestions would be helpful.

Thx,

Mike

Code in the activate event of a sheet:

If ActiveSheet.Name <> "Sand Box" Then
If DelSheet = True Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
On Error Resume Next
Application.DisplayAlerts = False
Call DeleteSheet(SheetToDelete)
'Sheets(SheetToDelete).Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True

DelSheet = False
SheetToDelete = ""
Sheets("Sand Box").Visible = False
Else
'Do Nothing
'Sheets("Sand Box").Visible = False
End If
Else
End If

Code in

Sub DeleteSheet(strSheetName As String)
'On Error GoTo 0
'Application.DisplayAlerts = False
'Sheets(strSheetName).Select
Sheets(strSheetName).Delete
'ActiveWindow.SelectedSheets.Delete
'ActiveSheet.Select
'Range("A10").Select
'Application.DisplayAlerts = True
End Sub

 

[Michael M]

You haven't set a sheet name to delete in this code ???
So the macro doesn't know which sheet to delete

Sub DeleteSheet(strSheetName As String)
'On Error GoTo 0
'Application.DisplayAlerts = False
'Sheets(strSheetName).Select
Sheets(strSheetName).Delete
'ActiveWindow.SelectedSheets.Delete
'ActiveSheet.Select
'Range("A10").Select
'Application.DisplayAlerts = True
End Sub

 

[mgCulver]

Hi Michael.

A sheet name is passed to the activate event of a sheet; in turn that calls the sub (Call DeleteSheet(SheetToDelete)) and passes the sheet name to the sub. 'strSheetName' is the variable.

If I step through the code, it does receive the correct name.

The fail occurs when I the sheet is deleted on this line of code - Sheets(strSheetName).Delete

Thx

 

[Michael M]

Ok, where have you set strSheetName as a variable in either code ??

 

[Michael M]

Are you trying to delete ALL sheets except "sandBox"
or jusr another sheet ?
What is the name of that other sheet ?

 

[mgCulver]

This Workbook allows a user to create multiple budgets/actuals for various activities for my client. Once they have completed the information on the sheet, it is posted to a history file and charted. When the data is posted, I need to delete the sheet. So the name of the sheet is variable depending on how the client names it on a user form. There can be as few as one sheet active at a time, or multiple sheets.

'SheetToDelete' is defined as a public variable. Therefore it is available through all sheets and changes when the user posts the data to the history file. The deletion routine does not need to have strSheetName defined as a variable as the sub is passed the sheet name from the call. [Call DeleteSheet(SheetToDelete); Sub DeleteSheet(strSheetName As String)].

Apparently this problem did not occur in Excel 2007 and prior; I am writing this in Excel 2013.

Thx