I want to populate a list box using a range in a different workbook and
folder. Have tried many things and many code snipets from other sources to
no avail

I am sure I will have to load the RowSource in code when the form Initializes.
My list box name is lb_Contact
The other workbook is 'ContactInformation'
The range is 'ContactName'

Any help will let me sleep tonight.

JB

Use the AddItem method instead of the RowSource property

http://www.dicks-blog.com/archives/2...stboxcombobox/


--
Dick Kusleika
MVP - Excel
Daily Dose of Excel
http://www.dicks-blog.com

JB in Kansas wrote:
I want to populate a list box using a range in a different workbook
and folder. Have tried many things and many code snipets from other
sources to no avail

I am sure I will have to load the RowSource in code when the form
Initializes. My list box name is lb_Contact
The other workbook is 'ContactInformation'
The range is 'ContactName'

Any help will let me sleep tonight.

Dick,
I have tried using AddItem but must be writing the code worng.

I have used this:
lb_Contact.AddItem
Workbooks("ContactInfromation").Worksheets("Contac t").Cells(2, 1000)

And this
lb_Contact.AddItem
Workbooks("ContactInfromation").Worksheets("Contac t").Range("ContactName")

Both give me an error message "Subscript out of range". What am I doing
wrong?

"Dick Kusleika" wrote:

JB

Use the AddItem method instead of the RowSource property

http://www.dicks-blog.com/archives/2...stboxcombobox/


--
Dick Kusleika
MVP - Excel
Daily Dose of Excel
http://www.dicks-blog.com

JB in Kansas wrote:
I want to populate a list box using a range in a different workbook
and folder. Have tried many things and many code snipets from other
sources to no avail

I am sure I will have to load the RowSource in code when the form
Initializes. My list box name is lb_Contact
The other workbook is 'ContactInformation'
The range is 'ContactName'

Any help will let me sleep tonight.

That is probably not a listbox error. My guess is that the workbook or the
worksheet is misspelled or missing. Try

MsgBox Workbooks("ContactInfromation").Worksheets("Contac t").Cells(2, 1000)

and see if it errs too.

HTH. Bst wishes Harald

"JB in Kansas" skrev i melding
...
Dick,
I have tried using AddItem but must be writing the code worng.

I have used this:
lb_Contact.AddItem
Workbooks("ContactInfromation").Worksheets("Contac t").Cells(2, 1000)

And this
lb_Contact.AddItem
Workbooks("ContactInfromation").Worksheets("Contac t").Range("ContactName")

Both give me an error message "Subscript out of range". What am I doing
wrong?

"Dick Kusleika" wrote:

JB

Use the AddItem method instead of the RowSource property


http://www.dicks-blog.com/archives/2...stboxcombobox/


--
Dick Kusleika
MVP - Excel
Daily Dose of Excel
http://www.dicks-blog.com

JB in Kansas wrote:
I want to populate a list box using a range in a different workbook
and folder. Have tried many things and many code snipets from other
sources to no avail

I am sure I will have to load the RowSource in code when the form
Initializes. My list box name is lb_Contact
The other workbook is 'ContactInformation'
The range is 'ContactName'

Any help will let me sleep tonight.

JB

I agree with Harald that "information" looks mispelled. When you get that
sorted, you can use either of these methods:

lb_Contact.List =
Workbooks("ContactInformation").Worksheets("Contac t").Range("ContactName").Value

or

Dim rCell as Range
Dim rRng as Range

Set rRng =
Workbooks("ContactInformation").Worksheets("Contac t").Range("ContactName")

For Each rCell in rRng.Cells
lb_Contact.AddItem rRng.Value
Next rCell

--
Dick Kusleika
MVP - Excel
Daily Dose of Excel
http://www.dicks-blog.com
Harald Staff wrote:
That is probably not a listbox error. My guess is that the workbook
or the worksheet is misspelled or missing. Try

MsgBox Workbooks("ContactInfromation").Worksheets("Contac t").Cells(2,
1000)

and see if it errs too.

HTH. Bst wishes Harald

"JB in Kansas" skrev i melding
...
Dick,
I have tried using AddItem but must be writing the code worng.

I have used this:
lb_Contact.AddItem
Workbooks("ContactInfromation").Worksheets("Contac t").Cells(2, 1000)

And this
lb_Contact.AddItem
Workbooks("ContactInfromation").Worksheets("Contac t").Range("ContactName")

Both give me an error message "Subscript out of range". What am I
doing wrong?

"Dick Kusleika" wrote:

JB

Use the AddItem method instead of the RowSource property


http://www.dicks-blog.com/archives/2...stboxcombobox/


--
Dick Kusleika
MVP - Excel
Daily Dose of Excel
http://www.dicks-blog.com

JB in Kansas wrote:
I want to populate a list box using a range in a different workbook
and folder. Have tried many things and many code snipets from
other sources to no avail

I am sure I will have to load the RowSource in code when the form
Initializes. My list box name is lb_Contact
The other workbook is 'ContactInformation'
The range is 'ContactName'

Any help will let me sleep tonight.

Dick, I finally got back to work on this. This spelling was a typo in the
message.

I have tried your suggestions and using both methods I still get a 'Run Time
error 9' 'Sub-script out of range' message.

Just to make sure my spelling is correct the following three lines work
(note the change I had to make to the first line).

Windows("ContactInformation.xls").Activate
Worksheets("Contact").Select
Range("ContactName").Select

I copied and pasted the workbook name, sheet name and range name.

I am a little lost my skills at best are low-average and this is blowing my
mind.

"Dick Kusleika" wrote:

JB

I agree with Harald that "information" looks mispelled. When you get that
sorted, you can use either of these methods:

lb_Contact.List =
Workbooks("ContactInformation").Worksheets("Contac t").Range("ContactName").Value

or

Dim rCell as Range
Dim rRng as Range

Set rRng =
Workbooks("ContactInformation").Worksheets("Contac t").Range("ContactName")

For Each rCell in rRng.Cells
lb_Contact.AddItem rRng.Value
Next rCell

--
Dick Kusleika
MVP - Excel
Daily Dose of Excel
http://www.dicks-blog.com
Harald Staff wrote:
That is probably not a listbox error. My guess is that the workbook
or the worksheet is misspelled or missing. Try

MsgBox Workbooks("ContactInfromation").Worksheets("Contac t").Cells(2,
1000)

and see if it errs too.

HTH. Bst wishes Harald

"JB in Kansas" skrev i melding
...
Dick,
I have tried using AddItem but must be writing the code worng.

I have used this:
lb_Contact.AddItem
Workbooks("ContactInfromation").Worksheets("Contac t").Cells(2, 1000)

And this
lb_Contact.AddItem
Workbooks("ContactInfromation").Worksheets("Contac t").Range("ContactName")

Both give me an error message "Subscript out of range". What am I
doing wrong?

"Dick Kusleika" wrote:

JB

Use the AddItem method instead of the RowSource property


http://www.dicks-blog.com/archives/2...stboxcombobox/


--
Dick Kusleika
MVP - Excel
Daily Dose of Excel
http://www.dicks-blog.com

JB in Kansas wrote:
I want to populate a list box using a range in a different workbook
and folder. Have tried many things and many code snipets from
other sources to no avail

I am sure I will have to load the RowSource in code when the form
Initializes. My list box name is lb_Contact
The other workbook is 'ContactInformation'
The range is 'ContactName'

Any help will let me sleep tonight.

JB in Kansas wrote:
Dick, I finally got back to work on this. This spelling was a typo
in the message.

I have tried your suggestions and using both methods I still get a
'Run Time error 9' 'Sub-script out of range' message.

Just to make sure my spelling is correct the following three lines
work (note the change I had to make to the first line).

Windows("ContactInformation.xls").Activate
Worksheets("Contact").Select
Range("ContactName").Select


Subscript errors are pretty easy to find, generally. Post the code you are
using and indicate which line gives the error. Is the listbox on a userform
or on a worksheet? Where is the code - standard module, userform module,
sheet module?


--
Dick Kusleika
MVP - Excel
Daily Dose of Excel
http://www.dicks-blog.com