Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: VBA: Using Input Box Cancel Button to Exit Sub

  1. #1
    Join Date
    31st May 2003
    Location
    Ramona, CA
    Posts
    75
    I have a sub routine that starts with a series of Input boxes. It would be very helpful if I could figure out how to exit the sub routine when the cancel button on any of the Input boxes is clicked. Does anyone have ideas?

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,433
    If the cancel button is clicked then InputBox returns a zero length string, so just check on that. For example,
    x = InputBox("Input Something")
    If x = "" Then Exit Sub

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    31st May 2003
    Location
    Ramona, CA
    Posts
    75
    There may be input boxes that are left blank when the OK button is clicked will that also exit the sub if I go this route?

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,433
    Yes, but you could perhaps get around this by having a default entry for each input box of " " (a space) and then after you checked for the cancel button you could check for the space and remove it or do whatever you would normally do with a blank entry.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    26th September 2011
    Posts
    1

    Re: VBA: Using Input Box Cancel Button to Exit Sub

    Try to use like this:


    sub inputbox_verification()

    text=inputbox("type the text")

    if StrPtr(text)=0 then
    'if it entenrs here then the user pressed "cancel"
    endif

    if text=""
    'if enters here the user left in blank
    end if

    if text<>""
    'if enters here the user entered some text
    end if

    end sub

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    23rd September 2011
    Posts
    73

    Re: VBA: Using Input Box Cancel Button to Exit Sub

    titarelli,

    excellent use of the strptr function there. Might I offer that a Select Case could tidy the code somewhat

    VB:
    Dim response As Variant 
     
    response = InputBox("Prompt", "Title") 
    Select Case StrPtr(response) 
    Case 0 
         'OK not pressed
        Exit Sub 
    Case Else 
         'OK pressed
         'Carry on your routine, variable response contains the InputText
    End Select 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    6th August 2010
    Posts
    23

    Re: VBA: Using Input Box Cancel Button to Exit Sub

    StrPtr does not seem to return 0 if using application.inputbox. Any other way to test for cancel using application.inputbox?

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: VBA: Using Input Box Cancel Button to Exit Sub

    Don-NS,

    Please do not post your question in threads started by others - - this is known as thread hijacking.
    Always start a new thread for *YOUR* question. If you find it helpful to clarify your needs you can include a link to this ( or any other ) thread.

    When starting a new thread be sure to give it a search-friendly title that aptly describes your need.
    The better your title define the thread the more relevant the results returned when searching.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Exit Macro If Cancel On InputBox Is Clicked
    By Pedrohern in forum EXCEL HELP
    Replies: 1
    Last Post: October 25th, 2007, 19:50
  2. Input Box to Cancel
    By Timbo in forum EXCEL HELP
    Replies: 3
    Last Post: January 25th, 2006, 01:27
  3. on cancel exit sub
    By ShawnIRQL in forum EXCEL HELP
    Replies: 2
    Last Post: May 17th, 2005, 05:58
  4. Exit Sub if User selects Cancel
    By jefsboys in forum EXCEL HELP
    Replies: 2
    Last Post: August 20th, 2004, 01:09
  5. VBA: Argh! Cancel button on input box
    By PNeely in forum EXCEL HELP
    Replies: 5
    Last Post: August 4th, 2003, 04:02

Bookmarks

Posting Permissions

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