Double-Click Listbox Selection Problem

Question

Hello all.

I am running into the following problem and cannot find a solution.

I have a Userform with 2 Listboxes. The Userform initializes with Listbox1 visible and Listbox 2 not visible. My intention is that when the user double-clicks a line item in the Listbox1, it then hides Listbox1 and shows Listbox2, which would be populated with data based on the selection line that was double-clicked.

The code that gets me there is as follows:

Private Sub List1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Set ListData = New MSForms.DataObject:

ListData.SetText Me.List1.List(Me.List.ListIndex, 0):

ListData.PutInClipboard:

Set ListData = New MSForms.DataObject:

ListData.GetFromClipboard:

MyName = ListData.GetText

Listbox1.Visible=False

Listbox2.Visible=True

Listbox2.Selected(0) = True

Userform_Initialize

end sub

This isolates the first column data of the selected row, sets the MyName variable to that value, and displays the new data (code contained in the Userform_Initialize sub) in Listbox2.

This code works perfectly.... however, there is one slight interface issue I am dealing with.

When Listbox2 shows, I want the top row of the listbox to be selected. The code for this normally is:

Listbox2.Selected(0) = True

And if I run all the same code above using a Command Button (i.e. select the row and click the button), instead of Double-Clciking the line, that also works perfectly.

However, when I use the DblClick code, the new form ALWAYS selects the same ListIndex number as was clicked in Listbox1. So, for example, if Listbox1 has 20 items in the list, and I double-click on row 5, then the code opens Listbox2 as it should, but selects Line 5 and not Line 0 (top line). Likewise, if I double-click Line 6, then Line 6 gets selected in the Listbox2. Only if I select an item n Listbox2 whose index number is greater than  the total number of items in Listbox2, does the top row get selected.

What's really strange is, running the EXACT SAME code from a button does not exhibit this behavior, and allows the Listbox2.Selected(0) = True to work as it should. I also tried linking the Double-Click sub to the Command Button code but that did not help.

For some reason, and I hope someone knows why, the Double-Click event is not allowing the code to run to select an item in a completely different Listbox even though the code that actually populates Listbox2 is in a completely different sub.

As a test, the one thing that I could get to work is if I inserted a Msgbox before the List2.Selected(0) = True line, then when it is dismissed, the first line in the Listbox gets properly selected. So, I don't know what that tells me, and having a Msgbox is not part of the code, but I am hoping someone on the board has an idea of how to get around this.

FYI, I have tried every trick I can think of, including preselecting the row on Form2 while it is hidden and actively Deselecting any row in List1 before running the code that changes the Listboxes. But none of these ideas are working.

Answer 1

Try doevents.

Answer 2

Still trying to work this problem.

So far, the only thing that works around this that I can find is putting up a msgbox. Once it gets dismissed the proper first line of the listbox is selected. Since I cannot have a msgbox in the code, I am wondering what other non-visible code I could use that achieves the same thing as the msgbox. In other words, something that would break the link from the DublClicl code the Listbox showing code.

Any idea?

Answer 3

Tried that first, and it does the same thing as: Listbox1.Selected(0) = True. Neither will set the selection to the top row when the source row is double-clicked. It's as if the selection index number is locked in when the item is double-clicked and it won't let go! Very strange behavior.

Answer 4

Try listbox2.listindex=0 instead.

Answer 5

One more thing:

In addition to the Command-Button working fine, the code also work fine with a simple Click or Change event. Clicking only once in Listbox1 will display Listbox2 and select the first line as intended.

For some reason, it's only the DblClick event that is causing this problem.