Listbox sub gives error when opening file

[tatoon]

Hi,

I have an excel file with three activex listboxes in cascade (the value of ListBox1 determines what will be shown in ListBox2 and so on).

ListBox1 gets the values directly via the ListFillRange property of the ListBox.

Then I have VBA code in the sheet, so when ListBox1 changes value, the values of ListBox2 will be automatically updated. This is the code I use:

Please Login or Register  to view this content.

The cell "L1" that I reference in the code usually has values like: "L2:L20"

The problem is that when I open the excel file, this sub seems to be executed automatically and it triggers an error popup:

compile error:
Method or data member not found

The VBA editor is automatically opened and:

- The name of the sub appears highlighted in yellow:
Private Sub ListBox1_Change()

- In the line with "Set lbtarget = Me.ListBox2"
The part of ".ListBox2" (so the last part of the line) appears highlighted in blue

If I ignore the error everything works fine, it just when opening the file. But when sharing the file with other people, this error is a source of problems.

If before I save the file, I select any cell (so anything other than the listbox), then it opens fine.
But if before saving the file, the last selected thing is a listbox, then I get the error.

Any idea why this error happen?

[Tinbendr]

Are you calling the userform directly from the Workbook_Open event?

[tatoon]

No, I dont have any module in the file, so no Workbook_Open sub.

That sheet has two subs (one from ListBox1 and another for ListBox2).

I have the 3 Listboxes defined in the sheet and the names are correct.

I dont know why that sub is being executed when the file is opened.
My guess was that the ListFillRange property of the Listbox triggers the ListBox1_Change sub, but using ListBox1_Click still runs this sub.

My feeling is that as soon as the file opens, ListBox2 is still not created (maybe takes some extra miliseconds?).

The sub only fails when opening. Otherwise, once I acknowledge the error when I get the popup, the sub works fine. So afterwards the sub finds ListBox2 without problems.

[Tinbendr]

My guess was that the ListFillRange property of the Listbox triggers the ListBox1_Change sub

Hmm, yes, that could be possible.

Then I would add some tests to the event.

Please Login or Register  to view this content.

[tatoon]

I finally solved it.

The Listboxes were located in order in one sheet, so one of top of the other.

Apparently, when opening the Excel file, as soon as the listbox at the top (ListBox1) was "created", it executed its VB code which calls ListBox2. But ListBox2 hasnt been "created" yet (since the Excel file is at the opening stage).

So I just reversed the names (not the positions). So the ListBox at the very top is now ListBox2 and the child ListBox below is ListBox1.
Apparently Excel now decides to first draw the ListBox at the bottom (so the child ListBox, now called ListBox1). So when it draws the ListBox at the top (now ListBox2), by the time it calls the other ListBox, this element has already been created.

Thanks for the support and ideas.