Cancelling But Macro Still Carries On
 

Good afternoon all,

I have the below macro. I entered an addition line for when the user
presses the cancel box, but the macro keeps going after I press
cancel. I'd like it to stop and go back to the main menu.


SORT_VENDOR

Dim sUsername As String
Dim sPrompt As String

sPrompt = "Please enter vendor name"
sUsername = InputBox(sPrompt, sTitle, sDefault)

Sheets("list").Select
Range("C3").Select
Cells.Find(What:=sUsername, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Select
If sUsername = "" Then
MsgBox "You cancelled the search."
Sheets("welcome").Select
Range("a1").Select

Exit Sub

Answer = MsgBox("Is this the contract/vendor you would like to
delete?", vbYesNo + vbInformation, "Please Confirm")
If Answer = vbYes Then
Select Case LCase(Range("B" & Selection.Row).Value)

Case "monthly"
Selection.Resize(12, 1).EntireRow.Delete
Case "quarterly"
Selection.Resize(4, 1).EntireRow.Delete
Case "yearly"
Selection.EntireRow.Delete
End Select
Exit Sub

Else
Cells.Find(What:=sUsername, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
End If

DELETE_EMPTYROWS

Cancelling But Macro Still Carries On
 

The Cancel on the InputBox dialog box does not stop the macro. It only
closes the InputBox without a value. If you want the Cancel button to cause
the macro to stop, then you need to add the following statement.

If sUsername = "" Then
Exit Sub
End If

Howeve, clicking OK with no entry in the InputBox also produces the empty
string "" and will exit the sub as well.

wrote in message
...
Good afternoon all,

I have the below macro. I entered an addition line for when the user
presses the cancel box, but the macro keeps going after I press
cancel. I'd like it to stop and go back to the main menu.


SORT_VENDOR

Dim sUsername As String
Dim sPrompt As String

sPrompt = "Please enter vendor name"
sUsername = InputBox(sPrompt, sTitle, sDefault)

Sheets("list").Select
Range("C3").Select
Cells.Find(What:=sUsername, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Select
If sUsername = "" Then
MsgBox "You cancelled the search."
Sheets("welcome").Select
Range("a1").Select

Exit Sub

Answer = MsgBox("Is this the contract/vendor you would like to
delete?", vbYesNo + vbInformation, "Please Confirm")
If Answer = vbYes Then
Select Case LCase(Range("B" & Selection.Row).Value)

Case "monthly"
Selection.Resize(12, 1).EntireRow.Delete
Case "quarterly"
Selection.Resize(4, 1).EntireRow.Delete
Case "yearly"
Selection.EntireRow.Delete
End Select
Exit Sub

Else
Cells.Find(What:=sUsername, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
End If

DELETE_EMPTYROWS

Cancelling But Macro Still Carries On
 

Why do you have vbInformation? It is not needed


vbYesNo = 4
vbInformation = 64
answer = vbYesNo + vbInformation = 68

If Answer = vbYes then
if 68 = 4 then
if False then

68 does not equal 4!!!!!!!!!!!!!!!!!!!!!!!!!!!


" wrote:

Good afternoon all,

I have the below macro. I entered an addition line for when the user
presses the cancel box, but the macro keeps going after I press
cancel. I'd like it to stop and go back to the main menu.


SORT_VENDOR

Dim sUsername As String
Dim sPrompt As String

sPrompt = "Please enter vendor name"
sUsername = InputBox(sPrompt, sTitle, sDefault)

Sheets("list").Select
Range("C3").Select
Cells.Find(What:=sUsername, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Select
If sUsername = "" Then
MsgBox "You cancelled the search."
Sheets("welcome").Select
Range("a1").Select

Exit Sub

Answer = MsgBox("Is this the contract/vendor you would like to
delete?", vbYesNo + vbInformation, "Please Confirm")
If Answer = vbYes Then
Select Case LCase(Range("B" & Selection.Row).Value)

Case "monthly"
Selection.Resize(12, 1).EntireRow.Delete
Case "quarterly"
Selection.Resize(4, 1).EntireRow.Delete
Case "yearly"
Selection.EntireRow.Delete
End Select
Exit Sub

Else
Cells.Find(What:=sUsername, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
End If

DELETE_EMPTYROWS