Application.InputBox Error

[Clif McIrvin]

Excel 2003 SP3; XP Pro SP3

Dim r As Range
Set r = Application.InputBox(prompt:="Please enter the range", Type:=8)


The above code throws Runtime error 424, "Object Rquired" when I try it.

The input box comes up, I can select cells with the mouse, which causes
the range address to appear in the input box. The error pops when I
click OK. (The locals window shows r as a range containing nothing.)

Any suggestions?

 

[Gord Dibben]

What occurs when you run this?

Dim r As Range
Set r = Application.InputBox(prompt:="Please enter the range", Type:=8)
MsgBox r.Address

I get the selected range returned to a message box.

Excel 2003 SP3; XP Pro SP3


Gord Dibben MS Excel MVP

 

[Don Guillett]

I got the same result as Gord

 

[Clif McIrvin]

Thanks, Gord and Don. I get the same error ... the debugger break line
is:

Set r = Application.InputBox(prompt:="Please enter the range", Type:=8)

Possible references issue? I unchecked a couple references that I have
added and re-compiled but that did not change my symptoms.

Next week I can try this on another computer in the office ... but don't
have that option today.

 

[Clif McIrvin]

I could add that

set r = selection

does yield expected results on my computer.

 

[Dave Peterson]

Are you sure you're not hitting the Cancel button?

I'd use:

Dim r As Range
....

Set r = nothing
on error resume next
Set r = Application.InputBox(prompt:="Please enter the range", Type:=8)
on error goto 0

if r is nothing then
msgbox "user hit cancel"
else
msgbox r.address(external:=true)
end if

 

[Clif McIrvin]

Worth a shot ... but same results with all four possible actions:
<enter>
<esc>
<OK> (mouse click)
<Cancel> (Mouse click)

Seems like there must be something goofy about my work environment ... I
tried closing and re-loading Excel; but havn't tried restarting windows
yet.

Now that I have the rest of the code in the macro tested and working
I'll come back to this issue.
In the meanwhile, the workaround is to break the macro in two where the
user selection is required.

 

[Dave Peterson]

How about some more details?

First, post a skinnied down test version of your code.

Then describe what range you selected and what shows up in the
application.inputbox.

Where is this code located? In a general module or what?

 

[Clif McIrvin]

Sure, more details coming up:

If I open a new workbook, add a code module and paste

Sub test()
Dim r As Range
Set r = Application.InputBox("Select range", Type:=8)
MsgBox r.Address
End Sub

into it, it works as expected.

However, if I paste the same code into the workbook in question I get
the "Object required" error on the "Set r= ..." line.

Upon additional investigation, I have come to the conclusion that I do
in fact have some error in the workbook in question (I have encountered
other, obscure, code failures in the past.)

My options appear to include a) live with the work-arounds I have become
familiar with or b) invest the time / learning curve to download
something like code cleaner (I have a reference from another post on my
"to do when I find the time" list) and see if I can discover what I have
done wrong.

Undoubtedly I have some bad seasoning in the spaghetti that accompanied
my original getting acquainted with VBA buried somewhere ... but as this
entire application is merely a "temporary" "make-do" while waiting for
the promised Access app some mysteries may never be solved.

Thanks much for your interest ... you prompted me to additional digging
that has at least identified the probable cause; if not the actual
culprit.

Thanks again to all who have helped me with this!

 

[Clif McIrvin]

Then describe what range you selected and what shows up in the
application.inputbox.

I failed to answer this question in my first reply.

The application.inputbox displays the expected selection.address value
corresponding to whatever I am selecting with the mouse.

The error then pops irrespective of which button I click.

 

[Shane Devenshire]

Hi,

1. Consider running a Code Cleaner utility against the offending WB:

http://www.appspro.com/Utilities/CodeCleaner.htm

by an old time Exceller Rob Bovey

2. Try using a UserForm with a RefEdit control if the above doesn't work.

3. Try using InputBox(Prompt) This is a different object and doesn't allow
you indicate the data type but that may not be a problem.

 

[Dave Peterson]

I really wanted to see the string that was returned--and the address of what you
selected.

But if you're experiencing errors with this workbook (or a single sheet within
that workbook), maybe it's time to recreate that workbook--or even single sheet.

I would guess that the corruption problem is in the worksheet--not within the
code. But you'll know for sure after you try the code cleaner.

 

[Dave Peterson]

Ps. I still think you should surround your application.inputbox with the "on
error" lines. It will help if the user hits cancel.

 

[Clif McIrvin]

Ps. I still think you should surround your application.inputbox with
the "on
error" lines. It will help if the user hits cancel.

Understood.

Thanks again for the interest and suggestions.