none
How to cleanly use APPLICATION.INPUTBOX?

    Question

  • Office 2010, VBA for Excel, Windows XP

    The following Excell VBA code is causing me a lot of frustration.  I'm hoping someone out there can help set me straight.

    1st concern: When a user presses "Cancel", why do I get "Error 424"?  How do I avoid or handle this?

    2nd concern: When the input field is blank and the user clicks on "OK" Excel shows a warning box regarding an error in a formula and won't proceed until either a cell is picked or "Cancel" is clicked.  I haven't found a way to handle this so the user doesn't see it or have to deal with this warning.  I'm hoping that it's possible!

    If I can get an explanation of what VBA is doing/thinking, it will certainly help my understanding.

    This code is the only code in Sheet1.

    Sub test()
    '
    Dim response1 As Range
    On Error GoTo ErrorHandler
    Set response1 = Application.InputBox(prompt:="Pick a cell.", Type:=8)
    If response1 Is Nothing Then
        MsgBox ("Nothing Choosen")
    Else
        MsgBox (response1.Address)
    End If
    Exit Sub
    ErrorHandler:

    MsgBox (Err.Number)
    Resume Next
    End Sub

    • Moved by Kee Poppy Tuesday, December 28, 2010 12:10 PM (From:Visual Basic General)
    Thursday, December 23, 2010 5:47 AM

Answers

  • #1.

    I use:

    Dim myRng as range
    set myRng = nothing
    on error resume next 'allow the cancel
    set myrng = application.inputbox(prompt:="my prompt", type:=8)
    on error goto 0

    if myrng is nothing then
      msgbox "User hit cancel"
    else
      msgbox myrng.address
    end if

    #2.  I think you'll have to train the user to not hit ok when they're not
    specifying a range.

    DragonTsaurus wrote:


    Office 2010, VBA for Excel, Windows XP

    The following Excell VBA code is causing me a lot of frustration.  I'm hoping someone out there can help set me straight.

    1st concern: When a user presses "Cancel", why do I get "Error 424"?  How do I avoid or handle this?

    2nd concern: When the input field is blank and the user clicks on "OK" Excel shows a warning box regarding an error in a formula and won't proceed until either a cell is picked or "Cancel" is clicked.  I haven't found a way to handle this so the user doesn't see it or have to deal with this warning.  I'm hoping that it's possible!

    If I can get an explanation of what VBA is doing/thinking, it will certainly help my understanding.

    This code is the only code in Sheet1.

    Sub test()
    '
    Dim response1 As Range
    On Error GoTo ErrorHandler
    Set response1 = Application.InputBox(prompt:="Pick a cell.", Type:=8)
    If response1 Is Nothing Then
        MsgBox ("Nothing Choosen")
    Else
        MsgBox (response1.Address)
    End If
    Exit Sub
    ErrorHandler:

    MsgBox (Err.Number)
    Resume Next
    End Sub

    --

    Dave Peterson

    • Proposed as answer by Bruce Song Wednesday, December 29, 2010 8:00 AM
    • Marked as answer by Bruce Song Wednesday, December 29, 2010 12:19 PM
    Tuesday, December 28, 2010 1:52 PM
  • Hi DragonTsaurus,

    I think that there does not exist a way to use the Application.InputBox without generating an error after clicking 'Cancel'. If you just use

    Application.InputBox "Pick a cell", "Pick", , , , , , Type:=8 , and not to set its return value to the variable, it will not encounter the error when clicking the 'Cancel' button. However, if you want to set the return value to a variable and then click 'Cancel', it indeed will encounter the error. The only method could be the Dave provided via On Error GoTo 0

     

    In addition, here are some articles for you to reference:

    http://www.ozgrid.com/VBA/inputbox.htm

    http://msdn.microsoft.com/en-us/library/aa195768(v=office.11).aspx

    http://www.mvps.org/dmcritchie/excel/inputbox.htm

    From the articles, I saw that some examples also use On Error GoTo 0 to handle the error.

    I hope these can help you and feel free to follow up.

    Best Regards,

     


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Bruce Song Wednesday, December 29, 2010 8:01 AM
    • Marked as answer by DragonTsaurus Wednesday, December 29, 2010 5:37 PM
    Wednesday, December 29, 2010 8:00 AM
  • Thanks Bruce,

    Application.DisplayAlerts takes care of one of my questions. 

     

    Dennis


    Dennis
    • Marked as answer by Bruce Song Thursday, December 30, 2010 2:54 AM
    Wednesday, December 29, 2010 5:38 PM

All replies

  • Thursday, December 23, 2010 6:00 AM
  • It is, and I didn't see a VBA forum.  I'd be happy if I knew where is was.
    Dennis
    Thursday, December 23, 2010 6:11 AM
  • #1.

    I use:

    Dim myRng as range
    set myRng = nothing
    on error resume next 'allow the cancel
    set myrng = application.inputbox(prompt:="my prompt", type:=8)
    on error goto 0

    if myrng is nothing then
      msgbox "User hit cancel"
    else
      msgbox myrng.address
    end if

    #2.  I think you'll have to train the user to not hit ok when they're not
    specifying a range.

    DragonTsaurus wrote:


    Office 2010, VBA for Excel, Windows XP

    The following Excell VBA code is causing me a lot of frustration.  I'm hoping someone out there can help set me straight.

    1st concern: When a user presses "Cancel", why do I get "Error 424"?  How do I avoid or handle this?

    2nd concern: When the input field is blank and the user clicks on "OK" Excel shows a warning box regarding an error in a formula and won't proceed until either a cell is picked or "Cancel" is clicked.  I haven't found a way to handle this so the user doesn't see it or have to deal with this warning.  I'm hoping that it's possible!

    If I can get an explanation of what VBA is doing/thinking, it will certainly help my understanding.

    This code is the only code in Sheet1.

    Sub test()
    '
    Dim response1 As Range
    On Error GoTo ErrorHandler
    Set response1 = Application.InputBox(prompt:="Pick a cell.", Type:=8)
    If response1 Is Nothing Then
        MsgBox ("Nothing Choosen")
    Else
        MsgBox (response1.Address)
    End If
    Exit Sub
    ErrorHandler:

    MsgBox (Err.Number)
    Resume Next
    End Sub

    --

    Dave Peterson

    • Proposed as answer by Bruce Song Wednesday, December 29, 2010 8:00 AM
    • Marked as answer by Bruce Song Wednesday, December 29, 2010 12:19 PM
    Tuesday, December 28, 2010 1:52 PM
  • Thanks Dave.

     

    I was really hoping there was a way to use the Application.Inputbox without generating an error at all.  With all of Microsoft's resources and experience, I am amazed that a cancel button generates an error!  I am equally amazed that "OK" with an empty box does also and one which can't be handled!  But I guess that's life with microsoft.

     

    Thanks for the reply.

     

    Dennis


    Dennis
    • Edited by DragonTsaurus Tuesday, December 28, 2010 7:58 PM change closure
    Tuesday, December 28, 2010 7:56 PM
  • Hi DragonTsaurus,

    I think that there does not exist a way to use the Application.InputBox without generating an error after clicking 'Cancel'. If you just use

    Application.InputBox "Pick a cell", "Pick", , , , , , Type:=8 , and not to set its return value to the variable, it will not encounter the error when clicking the 'Cancel' button. However, if you want to set the return value to a variable and then click 'Cancel', it indeed will encounter the error. The only method could be the Dave provided via On Error GoTo 0

     

    In addition, here are some articles for you to reference:

    http://www.ozgrid.com/VBA/inputbox.htm

    http://msdn.microsoft.com/en-us/library/aa195768(v=office.11).aspx

    http://www.mvps.org/dmcritchie/excel/inputbox.htm

    From the articles, I saw that some examples also use On Error GoTo 0 to handle the error.

    I hope these can help you and feel free to follow up.

    Best Regards,

     


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Bruce Song Wednesday, December 29, 2010 8:01 AM
    • Marked as answer by DragonTsaurus Wednesday, December 29, 2010 5:37 PM
    Wednesday, December 29, 2010 8:00 AM
  • Thanks Bruce,

    Application.DisplayAlerts takes care of one of my questions. 

     

    Dennis


    Dennis
    • Marked as answer by Bruce Song Thursday, December 30, 2010 2:54 AM
    Wednesday, December 29, 2010 5:38 PM