Error Trapping Question

[Guest]

I have this simple macro

Sub DeleteSheet()
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Delete
End Sub

How should I modify it so that if Sheet1 isn't present, it'll just move on.
I'll want to add more to this at a later time, so Exit Sub may not
necessarily be an option.

 

[Guest]

Try this...

Sub DeleteSheet()

Dim intSheetCounter As Integer

intSheetCounter = 1

If IsError(Sheets("sheet" & intSheetCounter).Activate) Then

intSheetCounter = intSheetCounter + 1

Else

ActiveWindow.SelectedSheets.Delete
intSheetCounter = intSheetCounter + 1

End If

End Sub

HTH

 

[Zack Barresse]

Hi there Barb,

With something as simple as deleting a sheet, you can probably get away with
an 'On error resume next' statement. Example:


Sub DeleteSheet2()
On error resume next
Sheets("Sheet1").delete
End sub


This may not be very well structured for anything larger and it certainly
doesn't really give you much to work with. If doing this more than one time
(deleting sheets) I recommend you test for it, which I generally do with
another routine. Example:


Sub DeleteSheet3()
if WsExists("Sheet1", "Book1.xls") then
workbooks("Book1.xls").Sheets("Sheet1").Delete
End sub

Function WsExists(wsName as string, Optional wbName as string) as boolean
On error resume next
if wbName = "" then wbName = Activeworkbook.name
WsExists = Len(Workbooks(wbname).Sheets(wsname).name)
End function


Is this what you were looking for?

HTH

 

[Guest]

Try something like this...

Sub DeleteSheet()
if sheetexists("Sheet1") then Sheets("Sheet1").Delete
End Sub

Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function

 

[Guest]

Sorry big mistake made on the last post.

Try this instead....

Sub DeleteSheet()

On Error Goto ErrHandler

Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Delete

ErrHandler:

If Err.Number = 9 Then

MsgBox "Sheet 1 does not exist"

end sub

 

[Tushar Mehta]

Use the untested

Sub DeleteSheet()
On Error Resume Next
Sheets("Sheet1").Delete
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Guest]

I get a message asking the following:

Data may exist in the sheet(s) selected for deletion. To permanently delete
the data, select DELETE.

How do I delete the sheet without this message?

 

[Zack Barresse]

You need to quelch the DisplayAlerts property of the application then..


Application.DisplayAlerts = False
'..
'.. code to delete
'..
Application.Displayalerts = True


HTH

 

[Guest]

I've changed it to

Application.DisplayAlerts = False
On Error Resume Next
Sheets("Sheet1").Delete
Application.DisplayAlerts = True

and all is good with the world. Thanks for your help.