access listbox on a different sheet

[cogar]

Hello,

I have a listbox on sheet "Mecury" and I want to access and change the listbox's data whenever I click on a button on another sheet called "Jupiter". VBA keeps giving an error and i suspect that its because the listbox is not a global item hence the error. How do i fix this?

Thank you.

[mikerickson]

What kind of listBox is it, ActiveX from the tool box or a Form control?
The list of either can be filled by a control on a different page, but the syntax is different.

[cogar]

Hello mikerickson,

Its a List Box from the "Control Toolbox" menu which you see on the main view of excel.

I can access it fine when i write the VBA code in the sheet which contains the List Box but not from other sheets.

Thanks a lot.

[mikerickson]

My Mac doesn't support ActiveX.
I think the syntax to get the value is

Please Login or Register  to view this content.

and similarly for other propertes and methods, like .AddItem.

[cogar]

is that Active-X? I thought its just a simple List Box.

How do you explain that its accessible using the normal method when the VBA code is on the same sheet but Excel throws an error when trying to access using VBA code on another sheet?

Thanks much.

[davesexcel]

is that Active-X? I thought its just a simple List Box.

How do you explain that its accessible using the normal method when the VBA code is on the same sheet but Excel throws an error when trying to access using VBA code on another sheet?

Thanks much.

I believe the ListBox from the controls toolbar is only accessible from the sheet it is created on, if you want access to the same ListBox on all sheets, a UserForm might be a better way to go

[cogar]

Hello davesexcel,

But i can't use a userform because i need the List Box to be embedded on the sheet.

Any suggestions? Thanks much.

[mikerickson]

Testing a work, this, in a normal module, worked for me

Please Login or Register  to view this content.