Trapping error with ActiveX ListBox and Autofilter

[dboone]

I use a multiple-selection enabled ActiveX listbox to drive an autofilter. It works well, as long as I am careful not to drag my mouse across several selection in the listbox. If I am careful to click-release each selection I can select as many/few items in the listbox as I want and the autofilter code works fine.

However, if I accidentally drag the mouse across selections in the listbox, an autofilter error is generated ("Run-time error '1004': AutoFilter method of Range class failed").

The code isn't really relevant. I cycle through the entities in the listbox, build an array, and stuff the array into the Worksheets.Range.AutoFilter function. I would imagine that my array isn't completely/correctly populated when I'm in the middle of a drag operation.

My question: In my listbox VBA function [Private Sub ListBox1_Change()] how can I ensure that the code is only executed when I release the mouse button? I envision that there must be a way to detect the root mouse event that triggered ListBox1_Change() and exit the sub if it isn't MouseUp (or whatever).

Thank you in advance,
Boone

 

[dboone]

EDIT: my ListBox1_Change function runs every time I click-down, move the mouse, or release the mouse, inside the listbox. I only want it to run when I release the mouse button. Thanks.

 

[hampi11]

Hello Boone,

I am tryting to write a macro that does what yours do, but can only get it work with single selection in the listbox.

Can you or anyone else help me with the code for multiple selection?

Thank you,

Hampi

I use a multiple-selection enabled ActiveX listbox to drive an autofilter. It works well, as long as I am careful not to drag my mouse across several selection in the listbox. If I am careful to click-release each selection I can select as many/few items in the listbox as I want and the autofilter code works fine.

However, if I accidentally drag the mouse across selections in the listbox, an autofilter error is generated ("Run-time error '1004': AutoFilter method of Range class failed").

The code isn't really relevant. I cycle through the entities in the listbox, build an array, and stuff the array into the Worksheets.Range.AutoFilter function. I would imagine that my array isn't completely/correctly populated when I'm in the middle of a drag operation.

My question: In my listbox VBA function [Private Sub ListBox1_Change()] how can I ensure that the code is only executed when I release the mouse button? I envision that there must be a way to detect the root mouse event that triggered ListBox1_Change() and exit the sub if it isn't MouseUp (or whatever).

Thank you in advance,
Boone

 

[Nordlander]

Hello Boone,

I had the same problem, but found a solution that solved my problems. Just change your VBA function from Listbox1_Change() to ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single).

Sorry for the late reply, but hopefully it can help someone else!

/Nordlander