Listbox Problem

[Guest]

I have a listbox LB and I need to have a sub called when and only when a
line in LB is clicked on.

I am having two problems with LB_Click( ):

1. If I click on the line that is already highlighted, LB_Click is not
called.

2. If my macro changes data in the worksheet from which LB derives its
data then LB_Click( ) is called even though nothing was clicked on.

Is there a way to have a sub that:
will be called EVERY time a line in LB is clicked on
(even in case 1 above)

AND

will not be called UNLESS a line in LB is clicked on
(even in case 2 above)?

Thanks for any help!

 

[Tom Ogilvy]

1) as you observe, clicking on an item already selected does not fire the
click event. Perhaps you could have the click event remove the selection so
the box is pristine for the next user selection.

2) break the link between the box and the data. Populate the box with
code.

 

[Guest]

Thanks. I'm not sure how to remove the selection. I tried setting the
ListIndex to -1 but that also seemed to fire the click event (of course I
could break the link as you said). Is there a better way to remove the
selection?

I sort of solved the problem by using LB_DblClick, it works exactly as
desired except that it requires the user to double click.

I'm just curious, is there a known reason why Microsoft designed the click
event to work this way

Thanks.

 

[Dick Kusleika]

You might consider the MouseDown event.

Thanks. I'm not sure how to remove the selection. I tried setting the
ListIndex to -1 but that also seemed to fire the click event (of course I
could break the link as you said). Is there a better way to remove the
selection?

You have to disable events if you make changes in code, as described here

http://www.dicks-blog.com/excel/2004/06/disabling_event.html

 

[Tom Ogilvy]

I would assume because if someone held down the mouse button while over the
listbox, the click event would fire continuously.

Try adding this to your code: (remove any setting of listindex in the click
event).

Private Sub ListBox1_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
ListBox1.ListIndex = -1
End Sub

it seems to allow you to select a selected item and fire the click event
(without firing the click event twice).