Lock listbox after CLICK event

[PaulZak]

I have a listbox with code that runs after user makes a selection (clicks item). It's a Form Control (vs. ActiveX type).

Right now, while the code runs (takes about 5 seconds to complete), user can immediately click another item on list and listbox selection changes (though click event doesn't re-run since it's still completing initial run). RESULT: If user clicks X then clicks Y while first click run, list has item Y selected but results of CLICK event's code show data/results from item X.

Instead of displaying a temporary warning telling user not to click another list item, I'd like to prevent the listbox from being changed until the first click's event code finishes. I've tried adding the following to the beginning of the listbox's CLICK event but no success:
1) Adding .enabled = false or .locked = false.
2) Used DoEvents after enable/lock code but no improvement
3) Tried moving focus out of list before disabling and locking. I
4) Adding Application.EnableEvents = False at beginning of event

If I run code to disable listbox for testing (no CLICK event running), it's disabled correctly. So I know my code is valid. The change just doesn't take place during the listbox's CLICK event's code.

And, when stepping through code, then Immediate Window shows enabled value is actually false after line runs but, again, the user still is able to click listbox and change selection.

How can I prevent lisbox changes while CLICK event runs?
Seems like this should be simple. What am I missing???
Maybe some creative alternative way to achieve this goal?

 

[Fluff]

How about

   Static DisableMe As Boolean
   If DisableMe Then Exit Sub
   DisableMe = True
   'your code here
   DisableMe = False

 

[PaulZak]

Fluff,

Thanks. I tried using a variable for this, as you suggested. But it doesn't work because the CLICK event code doesn't relaunch when user clicks a second list item while the first selection's CLICK event is running. The listbox selection still changes (which is the problem) and the CLICK event code doesn't trigger for that second selection. So, the variable never is checked/used.

That approach does let me prevent the user from clicking any other controls (when I add the variable check to their event code), like a CLOSE SCREEN button. I just doesn't prevent the listbox selection from changing. (Or, should I put this code in a different event for the listbox? Not sure which one?)

Give it a try. The scenario is fairly simple. Not sure why I can't find more out there about how to handle this. Surely others have listboxes the trigger code when a list item is selected. And, they'd not want user to proceed with another selection until event code is finished. Seems basic GUI design to me.

Any other ideas?

 

[Fluff]

What exactly do you mean by the listbox click event? Forms Controls do not have events, you simply assign a macro to run when they are clicked.
Or are you talking about activeX controls on a UerForm?

 

[PaulZak]

Fluff,
I have a form (object) in my Workbook file. (Several forms, actually)
On it I've added numerous controls using the VB Editor's "toolbox" screen. For example, the form has command buttons, labels, multipage tab... and this listbox.
The listbox control has numerous events including the CLICK that I mentioned, as well as ENTER, CHANGE, MOUSEDOWN, MOUSEUP, etc.
I'd include a screen shot but I see no way to attach .jpgs in this forum.
Clear?

I think you're referring to adding controls on top of Worksheets. These you assign a macro do. Again, the listbox control is on a FORM, not spreadsheet.

 

[Fluff]

Ok, you are running Activex controls on a userform, rather than a Forms Control listbox, hence the confusion.
I can see no way to prevent the user clicking the listbox whilst the code is running. How about moving that code to a Command Button instead?

 

[PaulZak]

Fluff,

Adding the control button is an interesting alternative. It would solve the problem if the code I moved to the button can successfully disable or lock the listbox when user clicks the button (and thus launches the code behind it) so the user cannot change the listbox selection while the button's code runs. Otherwise, the same problem still exists.

Cheers.

 

[Fluff]

I'm not sure you can do that, at least not easily.
You could hide the listbox whilst the code is running, but VBA stores the mouse clicks until the code is finished.
Best bet is to probably educate the users not to keep clicking the listbox

 

[PaulZak]

Fluff,

Actually, I could do this nicely using the .enable = false in button's CLICK event code (at beginning). I then enable listbox again at end of button's CLICK event code.

Listbox now has no code in any events.

Although adding the button means an extra click for the user, I think this is a decent solution.

 

[Fluff]

I'm glad that works for you, as for some reason it doesn't for me.