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

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, Apr 11 '18 at 01:59
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, Apr 11 '18 at 02:03
**Do not** post "a lot of code". You can [edit] your question (with the [edit] link) to add only the relevant code. (See "[mcve]".) 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, Apr 11 '18 at 02:13
...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, Apr 11 '18 at 02:13
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, Apr 11 '18 at 02:32
The option explicit errors that popped up after I added, have now all gone, however this listbox issue is only an issue when the book is opened, and not every time. Driving me crazy. The code above that is throwing the error is not the first mention of ListBox2 — Damian M, Apr 11 '18 at 04:41
The problem has got to be either: you don't have a worksheet called `Sheet3`, or you don't have an ActiveX listbox called `ListBox2`. — ashleedawg, Apr 11 '18 at 04:50
I do yes, I have not called it anything, other than its default name that is ListBox2. Thanks so much for assisting me here — Damian M, Apr 11 '18 at 04:52
Sheet3 is also called graphs. I tried With Graphs.ListBox2 and With Sheets("Graphs").ListBox2 — Damian M, Apr 11 '18 at 04:53
How do you know it's an ActiveX listbox, and how do you know it's called Listbox2. (Sounds like silly questions but its part of troubleshooting) — ashleedawg, Apr 11 '18 at 04:54
What do you mean Sheet3 is also called Graphs? A Worksheet has one name. All objects have only one name. If the worksheet that Listbox2 is on, is called Graphs, then you need to change that in your code. `With Graphs.ListBox2`. Also get rid of the `Dim ... As ListObject` line you added. — ashleedawg, Apr 11 '18 at 04:54
I added the box from the active x section. When I click on Design Mode, in the name box in the top left, it is called ListBox2. Not silly at all, anything to troubleshoot. I have renamed sheet3 to 'Graphs'. I also have Dim Graphs as Worksheet and Set Graphs = Sheets("Graphs") — Damian M, Apr 11 '18 at 05:02
See the answer below. It's *got* to be one of those things. However I would *not* recommend randomly adding variab\les like `Graphs`. Please remove anything you've done like that **or** for Sheet3 **or** for Listbox2 etc etc — ashleedawg, Apr 11 '18 at 05:11
See the answer below. It's *got* to be one of those things. However I would *not* recommend randomly adding variables like `Graphs`. Please remove anything you've done like that **or** for `Sheet3` **or** for Listbox2 etc etc. `Sheet3` and `Listbox2` should **not** be declared anywhere. — ashleedawg, Apr 11 '18 at 05:17

ashleedawg · Accepted Answer · 2018-04-11T05:34:21.083

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)

Listbox error on open

1 Answers1

Either:

More Information