Double Click Loads Userform, but Userform Somehow Inherits the Clicks

Bdra

New Member
Joined
Jul 19, 2008
Messages
41
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?
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Bdra

New Member
Joined
Jul 19, 2008
Messages
41
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.
 

shumcal

New Member
Joined
Jan 28, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I am also having this problem, and unable to find anything else about it. Have you had any success?
 

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
  1. 365
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

Can either supply the code for this?
 

shumcal

New Member
Joined
Jan 28, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.

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

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
  1. 365
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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

VBA Code:
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,917
Have you tried inserting a blank ListBox_DoubleClick event, to capture and ignore the double click?

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

shumcal

New Member
Joined
Jan 28, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 9, 2002
Messages
2,723
Had this something similar here...
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
Code:
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top