Exit when Cancel clicked.
 

Hi All

I have the code below which places user's inputs into the last-used cell in column C.

At present, if "Cancel" is clicked a blank row is added. I would like the routine to simply exit without doing anything if Cancel is clicked. I've googled these and have seen various ways of doing it but NOT in tandem with inputting to a cell.

Help! (Please)

'Determine emptyRow
emptyRow = ActiveSheet.Range("C7").End(xlDown).Row + 1

'Transfer information from form to Event Budget Inputs sheet
Cells(emptyRow, 3).Value = InputBox("Enter the new Category", "BizApp New Category")
Cells(emptyRow, 4).Value = InputBox("Enter the budgeted amount for the new Category", "BizApp New Category")

MsgBox "New Category added."

Exit when Cancel clicked.
 

Hi,

Am Mon, 6 Jun 2016 08:58:01 -0700 (PDT) schrieb :

At present, if "Cancel" is clicked a blank row is added. I would like the routine to simply exit without doing anything if Cancel is clicked. I've googled these and have seen various ways of doing it but NOT in tandem with inputting to a cell.

I can't reproduce this behaviour. I guess the user enters a space and
press OK.
You can check the InputBox for space:

emptyRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp)(2).Row

'Transfer information from form to Event Budget Inputs sheet
val1 = InputBox("Enter the new Category", "BizApp New Category")
If val1 = "" Or val1 = Chr(32) Then Exit Sub

val2 = InputBox("Enter the budgeted amount for the new Category",
"BizApp New Category")
If val2 = "" Or val1 = Chr(32) Then Exit Sub

Cells(emptyRow, 3).Value = val1
Cells(emptyRow, 4).Value = val2

MsgBox "New Category added."


Regards
Claus B.
--
Windows10
Office 2016

Exit when Cancel clicked.
 

Hi Claus

thank you! It works fine if they click cancel, but if they input something it's giving this error "run time error 1004 application-defined error or object-defined error"

Below is the full code including your additions.

Sub Events_Add_Category()
Dim emptyRow As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False
strSheetName = ActiveSheet.Name

'Make Event Budget Inputs sheet active
Sheets("Event Budget Inputs").Activate

'Transfer information from form to Event Budget Inputs sheet
val1 = InputBox("Enter the new Category", "BizApp New Category")
If val1 = "" Or val1 = Chr(32) Then Exit Sub

val2 = InputBox("Enter the budgeted amount for the new Category", "BizApp New Category")
If val2 = "" Or val1 = Chr(32) Then Exit Sub

Cells(emptyRow, 3).Value = val1
Cells(emptyRow, 4).Value = val2

MsgBox "New Category added."

Sheets(strSheetName).Select

Call Event_Sort_Category
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Exit when Cancel clicked.
 

Claus Busch wrote:

Am Mon, 6 Jun 2016 08:58:01 -0700 (PDT) schrieb :

At present, if "Cancel" is clicked a blank row is added. I would like
the routine to simply exit without doing anything if Cancel is clicked.
I've googled these and have seen various ways of doing it but NOT in
tandem with inputting to a cell.

I can't reproduce this behaviour. I guess the user enters a space and
press OK.
You can check the InputBox for space:

emptyRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp)(2).Row

'Transfer information from form to Event Budget Inputs sheet
val1 = InputBox("Enter the new Category", "BizApp New Category")
If val1 = "" Or val1 = Chr(32) Then Exit Sub

Or just:

If Trim(val1) = "" Then Exit Sub

val2 = InputBox("Enter the budgeted amount for the new Category",
"BizApp New Category")
If val2 = "" Or val1 = Chr(32) Then Exit Sub
^^^^ typo; should be val2

If Trim(val2) = "" Then Exit Sub

Cells(emptyRow, 3).Value = val1
Cells(emptyRow, 4).Value = val2

MsgBox "New Category added."

--
- What are you afraid of?
- Dying without purpose.

Exit when Cancel clicked.
 

Hi Auric,

Am Mon, 6 Jun 2016 16:50:47 -0000 (UTC) schrieb Auric__:

val2 = InputBox("Enter the budgeted amount for the new Category",
"BizApp New Category")
If val2 = "" Or val1 = Chr(32) Then Exit Sub
^^^^ typo; should be val2

thank you for the correction of my typo.


Regards
Claus B.
--
Windows10
Office 2016

Exit when Cancel clicked.
 

Hi,

Am Mon, 6 Jun 2016 09:50:36 -0700 (PDT) schrieb :

'Make Event Budget Inputs sheet active
Sheets("Event Budget Inputs").Activate

you forgot to inizialize emptyRow.
Change the line above:

'Make Event Budget Inputs sheet active
Sheets("Event Budget Inputs").Activate
emptyRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp)(2).Row


Regards
Claus B.
--
Windows10
Office 2016

Exit when Cancel clicked.
 

Gentlemen I thank you both, it's working perfectly now.

Have a great evening!

Steve