Listbox error on open

Question

Hello Stackoverflow community

I require help with a ListBox error.

I have two ListBoxes in a worksheet (single select) & not in a userform

ListBox1 - contains primary items

ListBox2 - contains sub items associated with the primary items

When a primary item is selected -

• If no sub items exist, ListBox2.Visible = False

• If sub items exist, ListBox2.Visible = True and .ListIndex = -1 (ready for sub item selection)

Code runs perfectly when using the workbook

Issue When workbook is opened, the macro linked to ListBox1 will run automatically and and error will pop up, highlighting in blue '.ListBox2'

Error = 'Compile Error: Method or Data Member not found'

The Error does not happen all the time, and does not matter if the workbook is saved with ListBox2 visible or not, or a sub item selected or not.

I have tried using on open workbook events, but the error comes before these events.

Please help

Added code where error occurs

Dim ListBox2 As ListObject 'Added this in to see if it fixes error but it has not

    With Sheet3.ListBox2
        .Height = 208.5
        .Width = 126.75
        .Top = 312.75
        .Left = 31.5
        .Visible = False
    End With

Answer 1

Either:

• You don't have a listbox called Listbox2, or,

• Listbox2 is not on a worksheet called Sheet3

Double check that you're referencing the objects by the correct names.

To confirm the name of the listbox:

• click Developer > Design Mode > Right click the listbox > Properties

• If it's an ActiveX listbox it will have a property list just like the one below:

• Click the name in the (Name) field in the properties dialog. Make sure there are no extra spaces at the beginning or end of the name.

To confirm the name of the worksheet:

Double-click the tab at the bottom of the worksheet. Make sure there are no extra spaces at the beginning or end of the name.

---

If you still can't see the problem:

• Rename Listbox2 to Listbox222 in the ActiveX properties dialog.

• Double click the tab for Sheet3 and type Sheet333.

• Change the reference that was causing the error to Sheet333.Listbox222

---

More Information

• Overview of working with Form Controls & Active controls programmatically (with VBA)