Listbox error on open

1
votes

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
1
vbaexcellistboxlistbox-control
Please add the relevant code to your question, indicating the line where you're getting the error. Note that a compile error is not the same as a runtime error. The code hasn't started to run yet since it can't compile properly. It's unlikely that it runs sometimes when there are no changes made to the workbook in between the attempts. – ashleedawg
Your right, I do not think the code is running, due to not compiling. I have tried saving the workbook with different selections, sometimes it opens without a hitch. I cannot pinpoint why sometimes it cannot compile. Sorry for the silly question, how can I enter code now that I have posted the question. It is quite a lot of code and the always highlights the same line, even thought listbox2 is referenced several times – Damian M
Do not post "a lot of code". You can edit your question (with the edit link) to add only the relevant code. (See "minimal reproducible example".) Perhaps your first step should be to add the line Option Explicit to the very top of the module (and every module, always) and then try compiling the code to see if that sheds some light on which variables/objects you have not properly declared. Note that it will probably produce several errors that you need to fix one at a time; this is a good thing. – ashleedawg
...One way or another, you are not referring to listbox2 properly, maybe an undeclared variable or object? (Also, the compiler will only stop on the first error in the module regardless of how many times you have used it... It "doesn't know" about the other errors yet because it hasn't made it past your first mistake yet.) – ashleedawg
I did not know what option explicit was until now, just googled and it is great advice. I have not managed to fix the initial error though – Damian M

1 Answers

0
votes

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:

img

  • 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.

img

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