Double Click Loads Userform, but Userform Somehow Inherits the Clicks

[Bdra]

I have run into some bug-like behavior in Excel 2010 that is driving me absolutely crazy. I can't find a way around it, and I can't find any mention of it online.

A userform is activated by double-clicking cells in certain specific columns on a worksheet. On the userform is a listbox with multiselect enabled; in other words, a list with a checkbox next to each entry.

For a while I was completely baffled to find that the userform sometimes came up with two items on the list selected. Not always, but sometimes. After much experimentation and pulling of hair I narrowed it down to the double-click event; those two clicks are somehow inherited by the userform.

If the cell that is double-clicked happens to be positioned right where the listbox loads, the listbox gets the clicks. If I click a cell further down in the column or scroll the sheet a bit to the side, in other words, if I jigger things so that the clicking takes place off-center, then everything loads correctly.

This is both annoying and stupid. I can't tell my client to "scroll a bit to the side and click very gently"; the userform needs to load clean, without random selections, regardless of where the clicks occur in the window area.

Here are some solutions that do NOT work:

1. Set Cancel = True under the double-click event. This prevents edit mode, but does not suppress the clicks.

2. Inserting DoEvents before the userform loads. This does not make the clicks go away.

3. Inserting a delay timer to hold off on loading the userform for a fraction of a second while the clicks dissipate into the ether. Alas, that doesn't seem to be how things work.

4. Chip Pearson's old Form Positioner (Form Positioner) that could be used to force a userform to position itself next to a defined cell. Unfortunately it doesn't seem to work correctly in newer Excel versions.

I'm stumped on this one. Any ideas?

 

[Jaafar Tribak]

Rather than a bug, I think, the issue here is that the by the time the second click arrives, the target userform is already on display.
Unfortunately, like you said, the code I posted will only work on Windows OS not in Mac.

 

[Mrock1]

You’re probably correct in the timing issue, but it’s curious that the double click event routine is triggered already in userform2. So based on that theory, the second click is still somehow active once userform2 is unloaded and then is detected by userform1. That would agree with the empirical evidence that a 1/2 second delay prevents the problem.

However, is still call that a bug fro MS as in an unexpected response or action.

inguess the 1/2 second delay remains

Thanks for your assistance.

 

[Jaafar Tribak]

Without using API calls, here is a workaround that worked for me quite nicely. It toggles the Locked Property of the ListBox when first showing the userform and the small timer delay is not noticeable at all.

UserForm Module:

Option Explicit

Private Sub UserForm_Initialize()
    Me.ListBox1.Locked = True
End Sub

Private Sub UserForm_Activate()
   Dim t As Single: t = Timer: Do: DoEvents: Loop Until Timer - t >= 0.1: Me.ListBox1.Locked = False
End Sub

 

[Mrock1]

Yeah. I think the 0.1 second delay is what’s doing it. Thx