MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Populate list box from a range in Add-In Sheet


Posted by Melanie Swarner on October 29, 2001 6:20 AM

I'm making an Add-In named SBUTemplate.xla I have a range in a sheet in the Add-In named SBUS I want to populate a list box with the contents of the range. I set the Control Source to the following, but got an error:

Workbooks("SBUTemplate.xla").Names("SBUS").RefersTorange

Any ideas?

Thanks!


Posted by Juan Pablo on October 29, 2001 6:30 AM

I think using

Range("SBUS").Address

or just

Range("SBUS")

will work

Juan Pablo

Posted by Melanie Swarner on October 29, 2001 1:01 PM

Add-In won't let me references the names

It works if I don't have the Add-In property checked as True. But as soon as I make it true, it hides the worksheets and the ranges and everything associated with them. That's why I'm using Workbooks("SBUTemplate.xla") I also tried ThisWorkbook, but it doesn't seem to work. I'm not very good at finding out what my options are for properties and methods of an object. I stumble through the help files, but they don't seem all that helpful.

Thanks for any hints!

Posted by Juan Pablo on October 30, 2001 7:45 AM

Re: Add-In won't let me references the names

If i turn the AddIn property on this works for me:

Workbooks("AddinBook").Sheets("Sheet1").Range("MyRange").Address

Try it and let me know how that goes

Juan Pablo

Posted by Melanie Swarner on October 31, 2001 5:05 AM

Object doesn't support this property or method

Juan,

I've got the following line of code now:

Workbooks("SBUTemplate.xla").Sheets("Sheet1").range("sbus").Address

You can see that it doesn't capitalize range as if it isn't a property of sheets. I did F1 on range and it said it is a propert of Worksheets, so I tried that, but it gives me the same error: "Object doesn't support this property or method"

It doesn't make sense. I've used the range property before with sheets. Is it because it's in the initialize event of a form? All commands work the same no matter what function or event they're being used in, right?

Thanks for your help.

Posted by Juan Pablo on October 31, 2001 8:29 AM

Re: Object doesn't support this property or method

I can't seem to replicate your results, but try this. Since the listbox conrolsource is a STRING, like "Sheet1!A2:A5" why don't you create first a string that takes out the Range address and then assign it to the listbox ? like:

TempString = "Sheet1!" & Workbooks("SBUTemplate.xla").Sheets("Sheet1").range("sbus").Address
ListBox1.ControlSource = TempString

This should return something like "Sheet1!A2:A5"

Hope this works.

Juan Pablo