Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Stop VBA when user selects Cancel

    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

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    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")
        If UserValue = Blank Then Exit Sub
        Range("B2").Value = UserValue
        Sheets("Sheet2").Select
        ActiveWorkbook.RefreshAll
        ActiveWorkbook.Save
    End Sub

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    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.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •