View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
JLGWhiz[_2_] JLGWhiz[_2_] is offline
external usenet poster
 
Posts: 1,565
Default 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