Runtime Error 381 Couldn't set list property, invalid array index

[Newbillian]

I opened a 97 spreadsheet in 2003 and now each time a user form is
initialized a runtime error comes up in the following line in the vba
code:

ComboDeptNum.List = Sheets("Definitions").Range("$J$1:$I$12")

It is the same thing if I specify a named range:
ComboDeptNum.List = Sheets("Definitions").Range("DeptList")


This exact same codes works fine in 97 and the security is set to low
in 2003. The number of columns is set propoerly but the list box just
won't load values. I'm a newbie to Excel vba but have more experience
with Access and never saw this 381 in Access.

Thanx in advance for help on this.

 

[jim c.]

i have excel2003 also. I tried to reproduce your error
and it is as you said... error. if all the code is doing
at your error is populating your list box, then use the
rowsource property

ListBox1.RowSource = "Sheet1!$J$1:$J$10"

this worked fine for me. but make sure address is in same
format as above. I didnt try all possible address syntax,
but this format seems the ony one to work. If you need to
cheat, Highlight the range and goto INSERT > NAME > DEFINE
and the address will be listed in the right format.

I'm only an amature, but I hope this helps...

 

[Jamie Collins]

(e-mail address removed) (Newbillian) wrote ...

a runtime error comes up in the following line in the vba
code:

ComboDeptNum.List = Sheets("Definitions").Range("$J$1:$I$12")

It is the same thing if I specify a named range:
ComboDeptNum.List = Sheets("Definitions").Range("DeptList")

Try using the Range object's Value property, rather than the Range
object itself or its default property e.g.

ComboDeptNum.List = _
Sheets("Definitions").Range("$J$1:$I$12").Value

ComboDeptNum.List = _
Sheets("Definitions").Range("DeptList").Value

I'm a newbie to Excel vba but have more experience
with Access and never saw this 381 in Access.

In the Immediate Window:

? Err.Number
381

? Err.Source
Forms.ListBox.1

Perhaps you never used the MS Forms ListBox object in MS Access.

Jamie.

--