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

Answer 1

sounds like a VBA question:

http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads

Answer 2

It is, and I didn't see a VBA forum.  I'd be happy if I knew where is was.

---

Dennis

Answer 3

#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

Answer 4

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

Answer 5

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.

Answer 6

Thanks Bruce,

Application.DisplayAlerts takes care of one of my questions. 

 

Dennis

---

Dennis