AndyEarl
08-06-2010, 10:59 AM
in developing a macro to allow an user to delete rows and worksheets associated with a user input works fine unless the user inputs an invalid worksheet or row number. my code is as follows: If the user input identifies a worksheet that does not exist the user gets a run-time error '9': How can I have the procedure recognize an input as an error and simply end the sub?
Sub Delete_Opportunity()
Dim UserIdentifiedRow As String
Dim UserInput2 As String
UserInput2 = MsgBox("Do you wish to Delete an Opportunity?", vbYesNo)
If UserInput2 = "" Then GoTo Step1000
If UserInput2 = 7 Then GoTo Step1000
If UserInput2 = 6 Then GoTo Step2
Step2: UserIdentifiedRow = InputBox("Enter the Item No. of Opportunity to Delete")
If UserIdentifiedRow = "" Then GoTo Step1000
Sheets(UserIdentifiedRow).Delete ' :dunno here is where I run into problems if the user input references a workbook that does not exist.
ActiveSheet.Unprotect Password:=""
Range("A11:A65536").Select
Selection.Find(What:=UserIdentifiedRow, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 0).Range("A1:AB1").Select
'Range("A11").Activate
Selection.Delete Shift:=xlUp
ActiveSheet.Protect Password:=""
Step1000: End Sub
Thanks
Sub Delete_Opportunity()
Dim UserIdentifiedRow As String
Dim UserInput2 As String
UserInput2 = MsgBox("Do you wish to Delete an Opportunity?", vbYesNo)
If UserInput2 = "" Then GoTo Step1000
If UserInput2 = 7 Then GoTo Step1000
If UserInput2 = 6 Then GoTo Step2
Step2: UserIdentifiedRow = InputBox("Enter the Item No. of Opportunity to Delete")
If UserIdentifiedRow = "" Then GoTo Step1000
Sheets(UserIdentifiedRow).Delete ' :dunno here is where I run into problems if the user input references a workbook that does not exist.
ActiveSheet.Unprotect Password:=""
Range("A11:A65536").Select
Selection.Find(What:=UserIdentifiedRow, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 0).Range("A1:AB1").Select
'Range("A11").Activate
Selection.Delete Shift:=xlUp
ActiveSheet.Protect Password:=""
Step1000: End Sub
Thanks