How to stop vba after user cancels input box

Question

I have written vba code which pops up an input box and then prints a range. I want to stop the code if the user presses the 'cancel' button. As of now, it prints it irrespective of the user action and wastes a lot of paper. Here is the code:

Sub Form()
ThisWorkbook.Sheets("FAQs").Activate
ActiveSheet.Range("A1048306").Select
ActiveCell.FormulaR1C1 = InputBox("SrNo1")
ThisWorkbook.Sheets("FAQs").Activate
ActiveSheet.Range("B1048306").Select
ActiveCell.FormulaR1C1 = InputBox("SrNo2")
ThisWorkbook.Sheets("FAQs").Activate
ActiveSheet.Range("C1048306").Select
ActiveCell.FormulaR1C1 = InputBox("SrNo3")
ThisWorkbook.Sheets("FAQs").Activate
ActiveSheet.Range("D1048306").Select
ActiveCell.FormulaR1C1 = InputBox("SrNo4")
ThisWorkbook.Sheets("FAQs").Activate
ActiveSheet.Range("E1048306").Select
ActiveCell.FormulaR1C1 = InputBox("SrNo5")
Columns("A:D").EntireColumn.Hidden = False
Range("A1048308:B1048359").PrintOut
Columns("A:D").EntireColumn.Hidden = True
ActiveSheet.Range("A1").Select
ThisWorkbook.Sheets("Spends Tracker").Activate
ActiveSheet.Range("A1").Select
End Sub

Edit:

Here's my new code which does not update cell A1048306 but still continues with the vba and updates Cells B1048306, C1048306, D1048306, E1048306 and prints the range.

Sub Form()
Dim strVale As String

Dim ws As Excel.Worksheet
Set ws = ActiveWorkbook.Sheets("FAQs")

strVale = InputBox("SrNo1")
If strVale = vbNullString Then
    MsgBox ("User canceled!")
    ThisWorkbook.Sheets("Spends Tracker").Activate
    ActiveSheet.Range("A1").Select
    Exit Sub
Else
    'Here we can use the objects range to set the formula.
    ws.Range("A1048306").FormulaR1C1 = strValue
End If

ws.Range("B1048306").FormulaR1C1 = InputBox("SrNo2")
ws.Range("C1048306").FormulaR1C1 = InputBox("SrNo3")
ws.Range("D1048306").FormulaR1C1 = InputBox("SrNo4")
ws.Range("E1048306").FormulaR1C1 = InputBox("SrNo5")
ws.Columns("A:D").EntireColumn.Hidden = False
ws.Range("A1048308:B1048359").PrintOut
ws.Columns("A:D").EntireColumn.Hidden = True
ActiveSheet.Range("A1").Select
ThisWorkbook.Sheets("Spends Tracker").Activate
ActiveSheet.Range("A1").Select

End Sub

score 1 · Accepted Answer · edited May 23 '17 at 12:04

1

Evaluate the return value instead of putting it into the cell formula.

change

ActiveCell.FormulaR1C1 = InputBox("SrNo1")

to something like this

Dim strVale as string
strVale = InputBox("SrNo1")

If strVale = vbNullString Then
    MsgBox ("User canceled!")
    Exit Sub
Else
    ActiveCell.FormulaR1C1 = strValue
End If

Apply this to each InputBox.

EDIT: for the second question

I changed your code a bit. Give it a try.

Selects and activates can cause things to get messy. See here. How to avoid using Select in Excel VBA macros

Best to avoid them.

I took out all the ThisWorkbook.Sheets("FAQs").Activate and declared an object for the sheet. Then you can use the object.

Option Explicit

Sub Form()
    Dim strValue As String

    'Here we are declaring and setting the object = to your FAQs worksheet
    Dim ws As Excel.Worksheet
    Set ws = ActiveWorkbook.Sheets("FAQs")

    strValue = InputBox("SrNo1")
    If strValue = vbNullString Then
        MsgBox ("User canceled!")
        ThisWorkbook.Sheets("Spends Tracker").Activate
        ActiveSheet.Range("A1").Select
        Exit Sub
    Else
        'Here we can use the objects range to set the formula.
        ws.Range("A1048306").FormulaR1C1 = strValue
    End If

    ActiveSheet.Range("B1048306").Select
    ActiveCell.FormulaR1C1 = InputBox("SrNo2")
    ActiveSheet.Range("C1048306").Select
    ActiveCell.FormulaR1C1 = InputBox("SrNo3")
    ActiveSheet.Range("D1048306").Select
    ActiveCell.FormulaR1C1 = InputBox("SrNo4")
    ActiveSheet.Range("E1048306").Select
    ActiveCell.FormulaR1C1 = InputBox("SrNo5")
    Columns("A:D").EntireColumn.Hidden = False
    Range("A1048308:B1048359").PrintOut
    Columns("A:D").EntireColumn.Hidden = True
    ActiveSheet.Range("A1").Select
    ThisWorkbook.Sheets("Spends Tracker").Activate
    ActiveSheet.Range("A1").Select
End Sub

Main change is

ActiveCell.FormulaR1C1 = strValue

became

ws.Range("A1048306").FormulaR1C1 = strValue

That way you don't have to worry about what the active or selected cell is.

edited May 23 '17 at 12:04

Community

1
1

answered Jan 22 '16 at 15:40

MatthewD

6,719
5
22
41

Glad to help. Keep on learning and let the SO community know when you need guidance. – MatthewD Jan 25 '16 at 01:42
Hi Mathew, I have another question. I played around with the code a bit. What happens now is if a user enters any value in the first box, it goes to the next box without updating cell "A1048306". – user1778266 Jan 29 '16 at 16:25
I am not able to paste my code here. Could you please show me how to add my code? I tried to enter 4 spaces before the code but it simply says you have exceeded the character limit. Thanks! – user1778266 Jan 29 '16 at 16:30
@user1778266 Just under your original question do you see the Share Edit Close Flag links? Click the edit and you can append your new question to the bottom of your old question. – MatthewD Jan 29 '16 at 16:59
@user1778266 You can't put much code here. Only one line surround it with `` – MatthewD Jan 29 '16 at 17:00
Thanks, I have added the new code to the bottom of the question. Could you please take a look at the code? Thanks, Monal – user1778266 Jan 29 '16 at 18:16
@user1778266 I made some changes to your code and posted it in my answer. See the comments in the answer and in the code. – MatthewD Jan 29 '16 at 18:41
1

Note that `strVale` could be declared as `as VbMsgBoxResult` which is the proper return type of the MessageBox object. `String` (obviously) works, too, but it's actually returning a long/integer constant like `vbYes`, `vbNo`, `vbCancel`, etc. – David Zemens Jan 29 '16 at 21:51
@MatthewD I have updated the code as per your suggestion. It has definitely helped to tighten the code. But it still does not update the Cell 'A1048306' but updates cells B1048306, C1048306, D1048306, E1048306 and prints the range. – user1778266 Jan 30 '16 at 13:35
Ahh, I see why. You have misspelled the variable declared as `Dim strVale` in stead of Dim strValue` you also have strVale two other places. I suggest you add `Option Explicit` at the top of you code above all other subs and functions. This will prevent this problem. – MatthewD Jan 30 '16 at 14:43

score 0 · Answer 2 · edited May 23 '17 at 12:04

Here's your code, tightened up. I suggest reading up on avoiding using .Select which helps a lot in readability and speed.

Sub Form()
Dim srOkCancel$
Dim i&

With ThisWorkbook.Sheets("FAQs")
    For i = 1 To 5
        srOkCancel = InputBox("SrNo" & i, vbOKCancel)
        If srOkCancel = vbNullString Then Exit Sub
        .Range(.Cells(1048306, .Columns(i)), .Cells(1048306, .Columns(i))).FormulaR1C1 = srOkCancel
    Next i

    .Columns("A:D").EntireColumn.Hidden = False
    .Range("A1048308:B1048359").PrintOut
    .Columns("A:D").EntireColumn.Hidden = True
    .Range("A1").Select
End With

ThisWorkbook.Sheets("Spends Tracker").Range("A1").Select

End Sub

The only thing is, I don't know what you're expecting as an input - will it be a formula? This won't really work as is, if you use like "=r1c1+1" as the input string. Please let me know what kind of data you expect to be entered and I'll fix it.

How to stop vba after user cancels input box

2 Answers2