vbaexcellistboxlistbox-control

Listbox error on open

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 -

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
Solution

  • Either:

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

    To confirm the name of the listbox:

    img

    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.

    img

    If you still can't see the problem:

    More Information