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?

 

[CSmith]

Most welcome! Thanks for letting us know I actually think it is more of a delay issue and I have an idea to counter the problem as I think the before double click is being registered and firing before the user actually releases the mouse button causing click to fire in the listbox. problem with solution I gave is it goes off the actual seconds so you can get a long full second or a short partial second before the listbox pops up.

CSmith, thanks, that works. It's a bit annoying as the delay is noticeable, but I'm very grateful for a functional solution.

mikerickson, that's a great idea, but sadly doesn't seem to work either. For completeness's sake I checked the Click and MouseDown events as well, and none of them were triggered, despite the listbox clearly registering the clicks. They seem to be passed "invisibly", after the method that called them, but before anything else. What's interesting is that when I generate the list dynamically upon loading the userform, the two clicks always select the first item and the item under the mouse, even when the first item is nowhere near the mouse. If the items are pre-selected when loading the form, then they will be unselected in the same pattern.

 

[CSmith]

Here is a follow up on my thoughts and appears to work
Updated Code below Quote:

Most welcome! Thanks for letting us know I actually think it is more of a delay issue and I have an idea to counter the problem as I think the before double click is being registered and firing before the user actually releases the mouse button causing click to fire in the listbox. problem with solution I gave is it goes off the actual seconds so you can get a long full second or a short partial second before the listbox pops up.

This is the Module Code

Public Sub ShowUserForm(frmName As String)   'Belongs in Module
  Application.EnableEvents = False
    VBA.UserForms.Add(frmName).Show
  Application.EnableEvents = True
End Sub

This code is attached to the UserForm.

Public lbItemClick As Boolean

Private Sub ListBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
  If Not lbItemClick Then
    Application.EnableEvents = False
    Cancel = True
    ListBox1.ListIndex = -1
    Application.EnableEvents = True
  End If
End Sub

Private Sub ListBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  lbItemClick = False
End Sub

Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  lbItemClick = True
End Sub

Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  If Not lbItemClick Then
    Application.EnableEvents = False
    ListBox1.ListIndex = -1
    Application.EnableEvents = True
  End If
End Sub

Private Sub UserForm_Initialize() ' Attached to UserForm
  lbItemClick = False
  Application.EnableEvents = False
    For i = 1 To 50
      ListBox1.AddItem i
    Next
  'ListBox1.ListIndex = -1
  Application.EnableEvents = True
End Sub

This is the Worksheet Code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'Your code with modifications...
    Cancel = True  ' Or do something else first?
    DoEvents
    ShowUserForm "frmMyUserForm"
End Sub

 

[CSmith]

Here is updated code for the userform as I didn't consider key list selection changes the first coding:
ONLY thing I did not add is a check for the active control.

'This code is attached to the UserForm
Public lbItemChange As Boolean

Private Sub ListBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
  If Not lbItemChange Then
    Application.EnableEvents = False
    Cancel = True
    If ListBox1.ListIndex <> -1 Then ListBox1.ListIndex = -1
    Application.EnableEvents = True
  End If
End Sub

Private Sub ListBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  lbItemChange = False
End Sub

Private Sub ListBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  lbItemChange = True
End Sub

Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  lbItemChange = True
End Sub

Private Sub ListBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  lbItemChange = True
End Sub

Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  lbItemChange = True
End Sub

Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  If Not lbItemChange Then
    Application.EnableEvents = False
    ListBox1.ListIndex = -1
    Application.EnableEvents = True
    lbItemChange = False
  End If
End Sub

Private Sub UserForm_Initialize() ' Attached to UserForm
  lbItemChange = False
  Application.EnableEvents = False
    For i = 1 To 50
      ListBox1.AddItem i
    Next
  Application.EnableEvents = True
End Sub

 

[shumcal]

Thanks NdNoviceHelp, that worked too. Similar mechanism to the Application.OnTime solution, in that each wait a second before continuing. Shame there's no solution that lets the form load in less than a second, but at least it works. I was playing around with moving the mouse to be off the listbox, but couldn't get it to work properly.

 

[CSmith]

Thanks NdNoviceHelp, that worked too. Similar mechanism to the Application.OnTime solution, in that each wait a second before continuing. Shame there's no solution that lets the form load in less than a second, but at least it works. I was playing around with moving the mouse to be off the listbox, but couldn't get it to work properly.

Did you not see the code I posted?

 

[NdNoviceHlp]

shumcal, you are welcome and thanks for posting your outcome. Dave

 

[Mrock1]

I realize this is an old post but I've had a similar experience, but kind of in reverse. I have a primary userform with a listbox occupying about half of that userform area. When I double-click on certain items in that listbox, I show a second userform, which is a single listbox that occupies all of that 2nd userform. I wish to use the 2nd userform's listbox_dlbclick routine to set a global public variable value and to unload that form using "Unload Me". However, if (as it does) the second userform's listbox is sitting directly over the primary userform's listbox, the primary userform listbox is inheriting the 2nd userform's double-click, but seemingly as a single click, selecting the item in the primary userform listbox that is beneath the item double-clicked on the 2nd userform. If I move the 2nd userform away from the primary, it doesn't do this and the listbox item I originally double-clicked on in the primary userform.

I've had to workaround this little annoyance by having the primary listbox_dlbclick routine move the selection back to the original item. This is clunky because the selection bounces from one item to the next and back again. It provides a workaround, but is far from ideal.

Why does Excel's double-click seem to linger and be inherited, in this case, by the underlying userform, but in the previous example, by the userform produced by the double-click on the cell? It's like at least one of the two clicks remains on the event stack and there doesn't seem to be any way to clear that stack. I've tried (as did my predecessor above) to insert various DoEvents commands and Cancel=Tue statements in both the userforms' listbox_dlclick routines. Haven't tried inserting a delay, but even that seems to be clunky.

For transparency the listboxes in each of the the userforms are called "ListBox1" and "LeavList" respectively. I am also using Windows APIs to set each userform to be always-on-top (SetWindowPlacement, among other API calls that calculate the final position). but I while those are used to reposition the userform window and set their on-top flag, I can't see how that might interfere with the basic double-click events in Excel.

I also have a CommandButton on the 2nd userform, set to Default=True, so the listbox item can be selected and Enter pressed, whereupon the CommandButton1_Click routine does the same commands as the LeaveList_DblClick routine ~ sets a variable and executes "Unload Me"

I'm using O365.

Hope someone can shed some practical light on this problem. Is it an "undocumented feature" of the VBE?

 

[Mrock1]

A quick update:
Inserting the timer loop in the 2nd userform's LeaveList_DblClick routine, before the Unload Me command avoids the problem:
Do Until Timer - t > 1
DoEvents
Loop

Still not ideal. There must be a reason that Excel does this, even if it's an unrecognized bug that it yet to be fixed by MS.

 

[Jaafar Tribak]

This issue can be easily solved by taking away the mouse capture from the userform inside the Worksheet_BeforeDoubleClick event as follows : (This will save you tons of code and no delays will be required plus no need to re-populate the listbox upon loading the userform.)

Worksheet Module

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function SetCapture Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
#Else
    Private Declare Function SetCapture Lib "user32" (ByVal hwnd As LongPtr) As Long
#End If

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    SetCapture Application.hwnd
    UserForm1.Show
End Sub

 

[Mrock1]

This is all well and good but won’t setcapture send all the mouse events to the underlying workbook in your example? How does that affect other mouse related events like click, double click and mouseover etc. in he second userform2?

My application doesn’t normally rely on being initiated by the user double clicking on the workbook. Instead when the workbook loads, the workbook_open routine initiates and OnTime command to show userform1. It is the double click event in the userform1 listbox1 that shows userform2. Userform2 has its own listbox and if that sits over the userform1 listbox, a double click on userform2 listbox results in userform1 listbox inheriting a click event.

I also use the mouse down, up and over events in userform1 to detect right clicks and ctrl right clicks, and hovers.

Maybe your workaround might work if the setcapture is used to direct the mouse events to userform1, but how would I do that without getting the hwnd via windows APIs which I need to avoid to be OS neutral? Other than the always n top function with Mac OSX doesn’t support at all, the code runs on OSX.

The big question is why does rbis occur in the first place. Is it a design feature for some obscure reason in the MS code, or simply a bug that MS just hasn’t fixed in years?

Thanks again