Range Error

[grNadpa]

I'm trying to Populate a ListBox's RowSource dynamically from different worksheet

Please Login or Register  to view this content.

The message on run is

subscript out of range

on the line

Please Login or Register  to view this content.

That range should be A4:A11

When I substitute that literal

Please Login or Register  to view this content.

, I get the same error.

Same result when I substitute bang (!Range) in place of the .Range

The reference tab: (name) shtReference; Name Reference (I've used both "Reference" and "shtReference" -- same result)
The basedata tab: (name) shtBaseData; Name BaseData (I've used both names -- but it never gets to that code)

Another curiosity is the the Sub Name is "UserForm" when the form's name is frmMaintain.

Regards,

grNadpa

[Richard Buttrey]

Hi,

It appears that you haven't got Option Explicit set. This forces you to declare variable names.
You've declared
rngCustomerName As Range

but are trying to set
rngCustomerNam

It's always good practice to use Option Explicit to avoid this very problem.

[grNadpa]

Thanks to Richard Buttery, the range spec is fixed. But still unable to populate my listbox.

Please Login or Register  to view this content.

Message:

Object does not support this property or method

on line

Please Login or Register  to view this content.

Probably obvious, but I don't see it.

Regards,

grNadpa

[Tinbendr]

Where is your listbox? On the userform or on the sheet?

[royUK]

Try this

Please Login or Register  to view this content.

Worksheets("BaseData") has nothing to do with the UserForm.

[grNadpa]

That does the trick.

So let me see if I understand.
The "Me" references the active form.
The "rngCustomerName.Address" provides the specific property of the range that ".RowSource" expects [which is why I got my error?] and
The "(external:=True)" specifies that the rngCustomerName.Address is not within the current sheet?

[royUK]

You are right, but for the RowSource Excel VBA expects a String not a Range, therefore use .Address which will return a String