Cancel Button Routine

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a button whose click event invokes 2 Input Boxes. The input boxes ask
the user for the first and last name of a person. The input boxes have an OK
and a Cancel button on them.

If the user clicks on either of the Cancel buttons, I want the programming
to stop and place the cursor in a specified cell.


I tried to do this on my own, but I haven't had any success. With or
without my "Cancel" coding, the sub runs in it's entirety and creates a name
in my list with just a comma in it.

How do I get the Cancel button click (whether it is the first input box or
the second input box) to discontinue running the macro and move the user's
cursor to cell AG3?

Here is the coding I have written so far:

If CommandButtonAddForeman.Caption = "Cancel" Then EndRoutine
Dim AnswerForemenFirst As String
Dim AnswerForemenLast As String
AnswerForemenFirst = InputBox("What is the Foreman's first name?",
"Add a new Foreman")
AnswerForemanLast = InputBox("What is the Foreman's last name?",
"Add a new Foreman")
Application.Goto Reference:="markerforemanadd"
ActiveCell.Value = "zzzzzz"
Selection.EntireRow.Insert
ActiveCell.Value = AnswerForemanLast + ", " + AnswerForemenFirst
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Application.Goto Reference:="ForemenToSort"
Selection.Sort Key1:=Range("SortRangeColumn"), Order1:=xlAscending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Application.Goto Reference:="markerforemanadd"
ActiveCell.Value = " "
Range("A1").Select
Range("AG3").Select



Private Sub EndRoutine()
Range("AG3").Select
End
End Sub

Thanks for any help you can offer.

Tofer
 
If CommandButtonAddForeman.Caption = "Cancel" Then EndRoutine
Dim AnswerForemenFirst As String
Dim AnswerForemenLast As String
AnswerForemenFirst = InputBox("What is the Foreman's first name?", _
"Add a new Foreman")
If AnswerForemenFirst = "" Then Exit Sub
AnswerForemenLast = InputBox("What is the Foreman's last name?", _
"Add a new Foreman")
If AnswerForemenLast = "" Then Exit Sub
Application.Goto Reference:="markerforemanadd"
ActiveCell.Value = "zzzzzz"
Selection.EntireRow.Insert
ActiveCell.Value = AnswerForemenLast + ", " + AnswerForemenFirst
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Application.Goto Reference:="ForemenToSort"
Selection.Sort Key1:=Range("SortRangeColumn"), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Application.Goto Reference:="markerforemanadd"
ActiveCell.Value = " "
Range("A1").Select
Range("AG3").Select


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
Bob, you solved it!

Thank you so much for your help.

Tofer

Bob Phillips said:
If CommandButtonAddForeman.Caption = "Cancel" Then EndRoutine
Dim AnswerForemenFirst As String
Dim AnswerForemenLast As String
AnswerForemenFirst = InputBox("What is the Foreman's first name?", _
"Add a new Foreman")
If AnswerForemenFirst = "" Then Exit Sub
AnswerForemenLast = InputBox("What is the Foreman's last name?", _
"Add a new Foreman")
If AnswerForemenLast = "" Then Exit Sub
Application.Goto Reference:="markerforemanadd"
ActiveCell.Value = "zzzzzz"
Selection.EntireRow.Insert
ActiveCell.Value = AnswerForemenLast + ", " + AnswerForemenFirst
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Application.Goto Reference:="ForemenToSort"
Selection.Sort Key1:=Range("SortRangeColumn"), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Application.Goto Reference:="markerforemanadd"
ActiveCell.Value = " "
Range("A1").Select
Range("AG3").Select


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

ToferKing said:
I have a button whose click event invokes 2 Input Boxes. The input boxes ask
the user for the first and last name of a person. The input boxes have an OK
and a Cancel button on them.

If the user clicks on either of the Cancel buttons, I want the programming
to stop and place the cursor in a specified cell.


I tried to do this on my own, but I haven't had any success. With or
without my "Cancel" coding, the sub runs in it's entirety and creates a name
in my list with just a comma in it.

How do I get the Cancel button click (whether it is the first input box or
the second input box) to discontinue running the macro and move the user's
cursor to cell AG3?

Here is the coding I have written so far:

If CommandButtonAddForeman.Caption = "Cancel" Then EndRoutine
Dim AnswerForemenFirst As String
Dim AnswerForemenLast As String
AnswerForemenFirst = InputBox("What is the Foreman's first name?",
"Add a new Foreman")
AnswerForemanLast = InputBox("What is the Foreman's last name?",
"Add a new Foreman")
Application.Goto Reference:="markerforemanadd"
ActiveCell.Value = "zzzzzz"
Selection.EntireRow.Insert
ActiveCell.Value = AnswerForemanLast + ", " + AnswerForemenFirst
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Application.Goto Reference:="ForemenToSort"
Selection.Sort Key1:=Range("SortRangeColumn"), Order1:=xlAscending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Application.Goto Reference:="markerforemanadd"
ActiveCell.Value = " "
Range("A1").Select
Range("AG3").Select



Private Sub EndRoutine()
Range("AG3").Select
End
End Sub

Thanks for any help you can offer.

Tofer
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cancel Input Box error 16
Input Box Cancel function 4
2 If/Then statements in 1 sub not working correctly 2
Input box cancel 1
ehandler 2
Input Box Q 3
Ehandler 1
No cancel on input 3

Back
Top