Compile Error with Application.InputBox

[rjprince83]

Hello, I am working on a project where I need to have the user select a range and pass that to my macro. It seems like the best way to do this is through the application.inputbox method, however it seems like excel does not even know that this method exits. Every time I try to use it, I get a compile error, "Method or data member not found".

Below is a sample code that generates the error for me:

Please Login or Register  to view this content.

I have not been able to find any similar issues through searching online. I'm guessing that I may need to add in a reference library, but haven't found any information on which one would be needed.

For reference, the VBA inputbox works fine, however that one does not have the "Type" argument to allow a range to be selected. If anyone has any thoughts on this, I would greatly appreciate it!

Thank you for your help.

[vcoolio]

Hello Rj,

Try switching the title and prompt around. The range InputBox requires an "order of things".
The title is optional, the prompt is required but needs to go first.

Cheerio,
vcoolio.

[rjprince83]

Thanks for the quick response vcoolio. I'm not sure when I switched that out of order, but in any event, I just switched it back as you recommended and I still get the same compile error. Though you did probably save me a headache down the line once I get this compile error fixed!

[vcoolio]

Hello Rj,

I can't recreate the error with a little test I did. Are any lines high-lighted in your code when the error arises?

I need to have the user select a range and pass that to my macro.

How are you doing this?

Cheerio,
vcoolio.

[Norie]

rjprince83

If you try the code in a new, blank workbook in a new instance of Excel do you still get the compile error?

[rjprince83]

Hello Rj,

I can't recreate the error with a little test I did. Are any lines high-lighted in your code when the error arises?

Yes, the .InputBox always gets highlighted when I get the error.

How are you doing this?

The intent was to do it through the input box. I don't know of a better way outside of making sure the user selects the range prior to starting the macro, but that seems like it would cause issues if they forgot to select it properly.

[Norie]

When you goto Tools>References... in the VBA IDE what's checked, also is anything marked as MISSING?

[rjprince83]

If you try the code in a new, blank workbook in a new instance of Excel do you still get the compile error?

Actually no, when I just tested this it worked fine in a new workbook. That's encouraging... do you happen to have any ideas why it wouldn't work in workbook I need it in?

Thank you for your help!

[Norie]

Have you named anything in the workbook where you are getting the error 'InputBox' or something similar?

Can you attach the problem workbook?

[rjprince83]

When you goto Tools>References... in the VBA IDE what's checked, also is anything marked as MISSING?

The checked ones are Visual Basic for Applications, Microsoft Excel 16.0 Object Library, OLE Automation, Microsoft Office 16.0 Object Library, Microsoft Forms 2.0 Object Library.

I'm not sure what you mean by anything marked as missing. There are dozens that are unchecked, I don't see anything that specifically says "MISSING" though.

[rjprince83]

Have you named anything in the workbook where you are getting the error 'InputBox' or something similar?

Can you attach the problem workbook?

I don't think I can attach the full workbook because there is potentially some proprietary as well as private client information in there. I just tried to strip out all the workbook data to be able to send it that over, and now it is working fine. So there is definitely something in the workbook itself that is causing it. I do have a bunch of named ranges, could that have something to do with it?

Thanks again for your help.

[Norie]

Have you named any of the named ranges, or anything else e.g. sub, variable etc, 'InputBox'?

[rjprince83]

Ohhhhh, I'm so dense. I named one of the worksheets "Application". I changed that and it fixed it. Thank you SO MUCH for your help, this was driving me absolutely crazy.

[Norie]

Sounds like that's the problem.

Note, the problem would only occur if you set the codename of the sheet to 'Application', you could still use that for the tab name.

[rjprince83]

Yes, you're correct. I had it in there as "Application" in the VBA codename, the display name was different. Thanks again, I really appreciate it!

[vcoolio]

Hello Rj,

Looks like Norie took you down the right path. Good to see it is all sorted.

Cheerio,
vcoolio.