jfgoodhew1
New Member
- Joined
- Oct 16, 2012
- Messages
- 19
Hi All, I'm struggling with InputBox in Excel 2010. I think I've set type to 8, for input range. But, I get Object error 424, Object Required. So, I added the line Set ProductNames = Nothing, to confirm it's a Range object using Set. Same problem. I also tried custom error handling, e.g. On Error GoTo 0; even the controversial Resume Next doesn't get past it. So, I've come to you guys for a helping hand 
I've put the code into ThisWorkbook module, and it's the only code in the project.
PS Just for context/overall aim: I would like to eventually use type 10, allowing either selected range or typed text to describe the named range (either its name or the cell references). I suspect with typed text I might have to then use a for loop to check all sheets for a named range of that name (my users will not understand the input method if I ask them to reference the sheet as well!) - but that's ok I can cope with that. It's the error I'm banging my head about.
Many thanks for any hints you can give me.
PPS Looking at that Msg, I prefer using the " _" option to extend to next line. Think I'll switch to that in future, far fewer characters and just as readable.

I've put the code into ThisWorkbook module, and it's the only code in the project.
PS Just for context/overall aim: I would like to eventually use type 10, allowing either selected range or typed text to describe the named range (either its name or the cell references). I suspect with typed text I might have to then use a for loop to check all sheets for a named range of that name (my users will not understand the input method if I ask them to reference the sheet as well!) - but that's ok I can cope with that. It's the error I'm banging my head about.
Code:
Option Explicit
Private Sub FindProductNames()
'Procedure to create a named range with all the products in it.
Dim Msg As String
Dim ProductNames As Range
Msg = "This step is to identify the names of your products."
Msg = Msg & vbNewLine & vbNewLine
Msg = Msg & "Please identify all of your product names, by one of the following options:"
Msg = Msg & vbNewLine
Msg = Msg & "1. Enter the name of the named range, ensuring it does include all your products."
Msg = Msg & vbNewLine
Msg = Msg & "2. Select the cells containing product names."
Set ProductNames = Nothing
Set ProductNames = Application.InputBox(Prompt:=Msg, Type:=8)
End Sub
Many thanks for any hints you can give me.
PPS Looking at that Msg, I prefer using the " _" option to extend to next line. Think I'll switch to that in future, far fewer characters and just as readable.