VBA - Prevent accidental selection of list items when opening userform

MattCSI

New Member
Joined
May 11, 2013
Messages
15
I have a macro which opens a userform with a listbox. I execute this macro by clicking a shape in a worksheet.

The problem is that sometimes when the userform pops up, the listbox catches the same mouse click from clicking the shape, and that selects an item on the list.

What is the best way to prevent this?
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,172
You can change the position of the Userform with .top and .left, I don't know if that would help.
You could also make the listbox hidden or empty until the shape is clicked (click on the shape would get/show the list in the form) or x seconds after the userform pops up.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
I'm not sure I understand.

You click on a shape that then clicks on a listbox that then opens a Userform

So the listbox in on your sheet?

Or are you saying when you click on a shape this action then opens your userform and you userform has a listbox which is then activated.

Does the listbox have a list of Userform names you can select from?
 

MattCSI

New Member
Joined
May 11, 2013
Messages
15
Sorry, I will try to explain better:

I have a sheet with a shape on it. The shape functions like a button.
When I click the shape, it opens a userform.
That userform has a listbox on it.
When the userform opens, it's positioned in such a way that the listbox is in front of the shape, which means the listbox is also under the mouse cursor.
Sometimes (not always), this causes the listbox item under the mouse cursor to become selected as the userform opens. I presume it's because I'm not letting go of the mouse button fast enough.

I have continued to work on it since I posted, and I ended up setting the listbox's .enabled property to false for 1 second after opening the userform. That seems to have solved the issue, but I was hoping there would be a more obvious solution.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
Well like someone else mentioned. Move the shape or move the listbox. That would be easy way.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,266
Do this 0.1 second delay in UserForm_Initialize work for you? No need to disable/enable the listbox.

Code:
    Dim t As Single
    
    t = Timer
    Do
        DoEvents
    Loop Until Timer > t + 0.1
 

MattCSI

New Member
Joined
May 11, 2013
Messages
15
Do this 0.1 second delay in UserForm_Initialize work for you? No need to disable/enable the listbox.

Code:
    Dim t As Single
    
    t = Timer
    Do
        DoEvents
    Loop Until Timer > t + 0.1
Yes, that works great, thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,099,061
Messages
5,466,351
Members
406,475
Latest member
suechetty

This Week's Hot Topics

Top