Code error

[Guest]

Does anyone know whats wrong with the code below?
I get an error in the third line where it starts with: Set inputrange=.....

Says Application defined or Object defined error



Private Sub SelectArguments_Click()
EngIndex = EngineSelect.List(EngineSelect.ListIndex, -1)
Set inputrange = Sheets("Output").Range("Eng_Range")
Set Inrng = Range(inputrange(1, EngIndex + 1),
inputrange(inputrange.Rows.Count, EngIndex + 1))
For Each Cell In Inrng
If IsEmpty(Cell.Value) Then
Exit For
Else
UserForm1.ListBox2.AddItem (Cell.Value)
End If
Next
UserForm1.Show
End Sub

 

[Guest]

I don't get that error as long as the named range exists. You're sure that
named range exists and is spelled correctly?

 

[Guest]

Yep the name range is spelled right. But hey I typed in not only the named
range but also gave more info on the sheet it is found in and that solved it.
Heres what I have and it works!!


Private Sub SelectArguments_Click()
EngIndex = EngineSelect.List(EngineSelect.ListIndex, -1)
Set W3 = Worksheets(3)
Set inputrange = Worksheets("Database").Range("Eng_Range")
Set Inrng = Worksheets("Database").Range(inputrange(1, EngIndex + 1),
inputrange(inputrange.Rows.Count, EngIndex + 1))
For Each Cell In Inrng
If IsEmpty(Cell.Value) Then
Exit For
Else
UserForm1.ListBox2.AddItem (Cell.Value)
End If
Next
UserForm1.Show
End Sub

 

[Guest]

When you give it a try make sure you write your own worksheet name, altough u
may index it as well.

 

[Guest]

Alternatively, when referencing worksheets in vba - use the code name (see
vba help for details). The VBA project explorer lists the worksheets by
codename with the tab name in parenthesis. So "Database" may appear as:

Sheet3 (Database)

and it could be referenced either by
Sheets("Database").Range("Eng_Range")
Worksheets("Database").Range("Eng_Range")
Sheets(3).Range("Eng_Range")
Worksheets(3).Range("Eng_Range")
Sheet3.Range("Eng_Range") 'Using the code name

The last example, however, is independent of the position of the worksheet
in the workbook and the worksheet tab name. Note the Worksheets and Sheets
collections will return different results depending on whether or not you
have Chart Sheets. Also, the code name cannot be changed programmatically.
You can change it to something more descriptive by clicking on the properties
button of the control toolbox toolbar (while in Excel environment) or through
the VBA properties window (in VBA environment).

Since the code name changes less often than the tab name or index number, I
rarely use tab names.