VBA Code if a user input in an inputbox causes error in a running macro

[AndyEarl]

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

[GTO]

Try:


Option Explicit

Sub Delete_Opportunity1()
Dim vntRowOrSheet As Variant

vntRowOrSheet = InputBox(Prompt:="Enter the Item No. of Opportunity to Delete", _
Title:="My Title")

If Not ShExists(vntRowOrSheet) Then Exit Sub

MsgBox "ShExists = True"

Application.DisplayAlerts = False
ThisWorkbook.Worksheets(vntRowOrSheet).Delete
Application.DisplayAlerts = True


'// I stop here. IMO, before now, you should have ActiveSheet referenced. //

' 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:=""
End Sub

Function ShExists(ByVal ShName As String, _
Optional WB As Workbook, _
Optional CheckCase As Boolean = False) As Boolean

If WB Is Nothing Then
Set WB = ThisWorkbook
End If

If CheckCase Then
On Error Resume Next
ShExists = CBool(WB.Worksheets(ShName).Name = ShName)
On Error GoTo 0
Else
On Error Resume Next
ShExists = CBool(UCase(WB.Worksheets(ShName).Name) = UCase(ShName))
On Error GoTo 0
End If
End Function

Hope that helps,

Mark

[AndyEarl]

Worked Great Thanks :yes :bow: