Building a list for Active X Listbox

[dflak]

In the attached there is a listbox in the vicinity of Cell D1 on the pivot sheet. I woulds like to populate it with the contents of Lookups!A2:A6. I realize I can do this without VBA, however, when I copy the sheet to a new workbook, the source data links back to the old workbook (the one that is posted here). If this workbook isn't available and open then the listbox is blank.

I need to populate it with "hard" values so it doesn't reference back to a workbook that won't be available to the end user. I think VBA is the only solution for this. If there is the equivalent of break link and use values I'll take that solution. If there is a way to assign the range to display to the local table instead of the one in the generating workbook, I'll take that too.

Here is my code:

Please Login or Register  to view this content.

I get the error Compile Error: Method or data member not found on:
shtP.ListBox1.AddItem "FOO"
It specifically higlights ListBox1

However this is the syntax all the articles I've found so far are using.

[torachan]

it likes this code better - i cannot see why - put it down Bill Gates.

Please Login or Register  to view this content.

[Greg M]

Hi there,

I think that life becomes a bit simpler when you work from the VBA CodeModule of the "Pivot" worksheet itself (rather than a standard VBA CodeModule), so see if the following code (entered in that CodeModule) does what you need:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[Greg M]

Hi again,

If you want to stick with your original approach, it seems you have to use the following code:

Please Login or Register  to view this content.

Hope this helps.

Regards,

Greg M

[dflak]

I found the root cause: If you already have the box populated with a range, it will fail. Remove the range and it works as all the other articles said it should.

Also I changed my plan of attack: instead of creating a workbook and adding only the pages I needed for the report, I made a copy of the workbook that generated everything, copy / pastespecial values only for some formulas (mostly lookups from pages that I didn't need copied) and removed the sheets I didn't need, hid the "background" pages and saved the copy.

Thanks everyone for chiming in. I've worked with listboxes in forms before, buy active X controls on the sheets is relatively new to me.