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?

 

[Bdra]

Oh, here are a couple more things that don't work:

5. Looping through listbox list and setting ListBox1.Selected(i) = False under the UserForm_Initialize routine. Apparently the clicks only hit after the userform has been initialized.

6. Using some trigger other than double-click. I am already using other worksheet events (SelectionChange, RightClick) for other purposes. Also, this is an existing application; my client has already trained their users to double-click in order to load this userform.

 

[Bdra]

bump

 

[shumcal]

I am also having this problem, and unable to find anything else about it. Have you had any success?

 

[CSmith]

Can either supply the code for this?

 

[shumcal]

Not really, as minimal code is needed. I was able to replicate in a fresh workbook by:

1. Creating a new userform, with a listbox that takes up the whole form (for simplicity)
2. Filling the listbox with junk entries (the numbers 1-50)
3. Setting the userform to show on double-click (see code below)

When the listbox popped up where the cursor was, it "saw" the click from the doubleclick and selected the item under the cursor. Any suggestions are still extremely appreciated: I've tried a similar range of suggestions to the original poster, with no luck.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    UserForm1.Show
End Sub

 

[CSmith]

This will eliminate the majority of it by inserting a slight delay, and disabling events along with resetting the listbox at initialization...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'Your code with modifications...
    Cancel = True  ' Or do something else first?
    Application.OnTime Now + TimeValue("00:00:01"), "'ShowUserForm ""frmMyUserForm""'"
End Sub

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


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

 

[mikerickson]

Have you tried inserting a blank ListBox_DoubleClick event, to capture and ignore the double click?

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    ' no code to do nothing
End Sub

 

[shumcal]

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.

 

[NdNoviceHlp]

Had this something similar here...

Phantom Listbox selection

I've made a trial to demonstrate. Add data to A1:B10 and a Userform with a Listbox. The following code is supposed to load the listbox with A1:A10 (and "TEST) on initialize and then when "TEST" is selected the Listbox clears and loads B1:10. Simple enough, but how to clear the phantom selection...

www.mrexcel.com

Jon Peltier resolved this indirectly with the following code (I'll see if I can find a link). I re-created your error as U outlined. This code fixed the problem. HTH. Dave

Private Sub UserForm_Initialize()
Dim t As Double
UserForm1.ListBox1.ListIndex = -1
t = Timer
Do Until Timer - t > 1
DoEvents
Loop
For i = 1 To 50
ListBox1.AddItem i
Next
End Sub

edit: here's the other link...

Excel VBA inconsistent errors when trying to copy and paste objects from Excel to Word

Hello All, I have some VBA code that will sometimes run just fine and sometimes will give an error. I am getting very frustrated and in need of some help ... I have tried various things, but just cannot find a solution. First ... what I am trying to do ... I have an embedded Word document...

www.mrexcel.com