+ Reply to Thread
Results 1 to 12 of 12

InputBox "Cancel" returns error

  1. #1
    Forum Contributor
    Join Date
    11-10-2009
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    448

    InputBox "Cancel" returns error

    With input boxes, is there something that allows the user to press "Cancel" without getting an error message.

    I have an InputBox Method, the other method is an InputBox Function. There seems to be solutions for the latter but I can't find anything for this.

    My selection is a Range.

    The code I have takes a range and copies it elsewhere, making a modification to the string at the same time.


    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,008

    Re: InputBox "Cancel" returns error

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 07-26-2018 at 02:28 AM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Contributor
    Join Date
    11-10-2009
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    448

    Re: InputBox "Cancel" returns error

    It's not quite working.
    I don't think it likes the statement "if a range Is Nothing", just as a hunch.

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,008

    Re: InputBox "Cancel" returns error

    I can't diagnose "not quite working". What did you do (show your whole code with the new changes), and describe what exactly happens? If it errors, give the error description, and press the debug button and say what line is highlighted.
    Last edited by AlphaFrog; 07-26-2018 at 06:48 AM.

  5. #5
    Forum Contributor
    Join Date
    11-10-2009
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    448

    Re: InputBox "Cancel" returns error

    I get an error message: Run-time error '424' Object required
    The code is shown below.

    The debug highlight is at the line: Set Range2 = Application.InputBox("Paste To", xTitleId, Range2.Address, Type:=8)

    Please Login or Register  to view this content.

  6. #6
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,202

    Re: InputBox "Cancel" returns error

    Each of the InputBox lines needs to be inside a On Error Resume Next pair to handle the Cancel button.
    Mimic the code for getting Range1 when getting Range2.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  7. #7
    Forum Contributor
    Join Date
    11-10-2009
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    448

    Re: InputBox "Cancel" returns error

    It's still not working.
    This time there's no error message but the data gets pasted anyway, even though Cancel is pressed twice.
    It should actually Exit after the first Cancel.


    Please Login or Register  to view this content.

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,097

    Cool Re: InputBox "Cancel" returns error


    Hi !

    Instead of On Error statement just use a Variant variable and check the result when cancelling …

  9. #9
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,008

    Re: InputBox "Cancel" returns error

    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    11-10-2009
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    448

    Re: InputBox "Cancel" returns error

    @AlphaFrog, your solution at post #9 works as intended.
    Thanks very much.

  11. #11
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,202

    Re: InputBox "Cancel" returns error

    Quote Originally Posted by Marc L View Post

    Hi !

    Instead of On Error statement just use a Variant variable and check the result when cancelling …
    That will work with Application.Match, which returns an error value when the search term is not there, but for the Application.InputBox issue, there will still be a run-time error when the user presses cancel.
    Application.InputBox returns the Boolean value False when cancel is pressed.
    The keyword Set in

    Please Login or Register  to view this content.
    will cause an error when the user presses cancel, but is required if the user doesn't

  12. #12
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,097

    Re: InputBox "Cancel" returns error


    Yes thanks, my bad as I misread / did not see that was the Excel InputBox and not the VBA one …

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to avoid "Cancel" with Application.InputBox
    By erice in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-05-2018, 11:09 AM
  2. [SOLVED] SaveAs "Cancel" Error
    By liquidmettle in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-11-2018, 01:18 PM
  3. [SOLVED] Code Clean-Up: Delete "False" worksheet created when "Cancel" is chosen in Input Box
    By Kenny Blackwell in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 08-31-2015, 10:00 AM
  4. [SOLVED] Using winhttp.winhttprequest.5.1 retrieving web data returns "Server Error 405"
    By lubbamkt in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-31-2015, 01:08 AM
  5. Me.Controls.Add returns "Compile error, method or data member not found"
    By mattisch in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-28-2007, 07:53 AM
  6. [SOLVED] Formula format in excel that returns an "error" if there is a blan
    By Ken Proj mgr in forum Excel General
    Replies: 5
    Last Post: 04-12-2006, 12:25 PM
  7. [SOLVED] Compile Error in Excel 2004 when Inputbox contains "VBCRLF"
    By QTP Professional in forum Excel General
    Replies: 1
    Last Post: 11-18-2005, 07:50 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1