Stop VBA when user selects Cancel

Topic

New Reply

What code can I use to stop VBA from processing when the user selects the Cancel button option in the InputBox?

Sub RefreshPT()
‘
‘ RefreshPT Macro
‘
Sheets(“Control”).Activate
Range(“B2”).Select
UserValue = InputBox(“Enter Date in MM/dd/yyyy format”)
Range(“B2”).Value = UserValue

Sheets(“DaySum”).Select
ActiveWorkbook.RefreshAll

ActiveWorkbook.Save

End Sub

Reply | Quote

Replies

Arcturus,

Code:

Option Explicit

Sub TestIB()

   Dim zAns As String
   
   zAns = InputBox("This is a test..Enter something.")
   
   If zAns = "" Then
     MsgBox "User Pressed Cancel!" & vbCrLf & _
            "or did not enter a value!", vbOKOnly + vbInformation, _
            "Inputbox Result:"
     Exit Sub
   Else
   End If
   
   MsgBox "User Did NOT press Cancel!", vbOKOnly + vbInformation, _
          "Inputbox Result:"
   
End Sub

Note: If the user clicks OK but does not supply a value it is the same as a Cancel as InputBox returns a zero length string in either case!

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

You could also use the line highlighted in blue:

Code:

Sub RefreshPT()
    Sheets(“Sheet1”).Activate
    Range(“B2”).Select
    UserValue = InputBox(“Enter Date in MM/dd/yyyy format”)
    [COLOR=”#0000FF”]If UserValue = Blank Then Exit Sub[/COLOR]
    Range(“B2”).Value = UserValue
    Sheets(“Sheet2”).Select
    ActiveWorkbook.RefreshAll
    ActiveWorkbook.Save
End Sub

Reply | Quote

Or equally:

Code:

    If UserValue = fish Then Exit Sub

since Blank is just an undeclared variable in that code.

FWIW, you can distinguish between the user pressing Cancel or pressing OK with a blank input:

Code:

    uservalue = InputBox("Enter Date in MM/dd/yyyy format")
    If StrPtr(uservalue) = 0 Then
        MsgBox "you pressed Cancel"
    ElseIf uservalue = "" Then
        MsgBox "you didn't enter anything then pressed OK"
    Else
        MsgBox "you entered " & uservalue
    End If

Reply | Quote

Thanks for the solutions. I tried all of them. In the end I’ll use Maudibe’s solution for expediency, but I want to try to use RetiredGeek’s and Rory’s in in the future. Maybe something to loop back if the user cancels.

Reply | Quote

Just bear in mind that if you use Option Explicit – and you should – that solution won’t work since, as I said, Blank is just an undeclared variable and not an intrinsic VBA value. Of course you’ll have to declare your variables too!

Reply | Quote