Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cancelling AutoFilter in a macro | Excel Programming | |||
Text does not carries Over | Excel Worksheet Functions | |||
Stop text carries over | Excel Worksheet Functions | |||
Cancelling a macro | Excel Programming | |||
end Macro after cancelling open dialog | Excel Programming |