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?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,176
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
17,575
Office Version
  1. 2013
Platform
  1. 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
17,575
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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,752
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,129,783
Messages
5,638,301
Members
417,020
Latest member
MSVII

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