Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Trapping Question
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Trapping Question
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 "Barb Reinhardt" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Trapping Question
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 -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Barb Reinhardt" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Trapping Question
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 -- HTH... Jim Thomlinson "Barb Reinhardt" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Trapping Question
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 "Barb Reinhardt" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Trapping Question
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 In article , says... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Trapping Question
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? "Tushar Mehta" wrote: 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 In article , says... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Trapping Question
You need to quelch the DisplayAlerts property of the application then..
Application.DisplayAlerts = False '.. '.. code to delete '.. Application.Displayalerts = True HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Barb Reinhardt" wrote in message ... 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? "Tushar Mehta" wrote: 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 In article , says... 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Trapping Question
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. "Tushar Mehta" wrote: 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 In article , says... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Quick question on Error Trapping | Excel Programming | |||
Error Trapping | Excel Programming | |||
error trapping | Excel Programming | |||
trapping error | Excel Programming | |||
error trapping | Excel Programming |