Help with listbox control events

[TrendyProgrammer]

Hi,

I'm experiencing some annoying problems with the listbox activex
control that I'm hoping someone with greater experience than I can
help me out with.

In my Excel spreadsheet, when the user makes a selection in the
listbox, the Listbox_Change() event (which contains no code) is
triggered and then immediately after, the Listbox_Click() event is
triggered. This is desired functionality. However, the problem I am
facing is that these events execute multiple times in succession
suggesting that the system is in a loop. In other words, the sequence
of events are as follows:
1. User makes a selection in the listbox
2. Listbox_Change() event fires and runs to completion (even though
there is no code here)
3. Listbox_Click() event fires and runs to completion
4. Steps 2 and 3 repeat approximately 10-15 times
5. Focus and control returned back to Excel spreadsheet containing the
control.

The code in the Click event is pretty simple:

'----------------------------------
Private Sub LikeItemsListBox_Click()
'selections made to the like items'control

Application.EnableEvents = False
LikeItemsListBox.enabled = False

Activesheet.EnableCalculation = True

Activesheet.Range("B2").value=Activesheet.range("B56")

exitthissub:

LikeItemsListBox.enabled = True
Application.EnableEvents = True

End Sub
'---------------------------------

Relevant properties of the listbox are given below:
Name: LikeItemsListBox
Autoload: False
BoundColumn: 1
Enabled: True
LinkedCell: $B$56
Locked: True
Multiselect: 0-fmMultiSelectSingle
Textcolumn: -1
Value:

From my research on this topic, it seems that the Change and Click

events are triggered when (1) the user clicks in the listbox or (2)
when the value of the listbox changes in code. This makes sense.
However, when I run the code through the debugger, the value of the
listbox never changes - it remains the same!

Any ideas?

Thanks a lot for your help.

Amit

 

[Tom Ogilvy]

If B2 is in the listfillrange of the listbox, then you need to break that
connection and load the box with code.

 

[TrendyProgrammer]

Hi Tom,

Thanks for your help. But, B2 is not in the listfillrange... in fact,
the listfillrange is set to a named range that refers to data on an
entirely different sheet!

Amit

 

[Tom Ogilvy]

Set calculation to manual and disable Excel events and see if the problem
goes away.

If it does, then there is some relation to your action of populating B2 and
a range the listbox is associated with.