VBA Inputbox Type 8 (range), 424 Object Required error, option explicit

[jfgoodhew1]

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.

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.

 

[Kyle123]

I've never used an input box like that, but it seems to not like the line breaks, this works for example

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
    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(Msg, Type:=8)


End Sub

 

[jfgoodhew1]

No ways... That is completely weird. But THANK YOU! You have solved a mystery that I've been facepalming over for hours.

Without line breaks, it's going to get really messy with all the instructions I want to include, so I suspect my approach will change, making users input things into a Welcome Worksheet, and switching over to the actual dashboard after they've completed that.

Really, many thanks. Why that's not in the documentation about InputBoxes I'll never know.

 

[Kyle123]

New one to me too, odd error message too. Ah well, glad you've got it sorted