ListBox Click event not working

[dinamike1]

I currently have a set of ListBox controls on a worksheet tab. They are all configured as multi-select and i have the values populated via the ListFillRange properties. Each listbox has an '{All}' option; when the user clicks on this value I want to de-select all other previously selected values. The code for this is straightforward enough, but I cannot get it to fire using the _Click event. Does anyone know why this is not working?

Additionally, I attempted to use the _Change event but quickly got caught in recursive loops which obviously is not going to cut it.

 

[erik.van.geit]

Hi,
please post the code anyway; it's easier to test things.
kind regards,
Erik
PS: Forms control or ActiveX?

 

[dinamike1]

Following is my code. I haven't been able to test it yet, as I cannot get the _Click event to fire. I have done everything I can think of to activate it, but to no avail. Thanks for your help.

Private Sub lstVintage_Click()

Dim i As Integer

With lstVintage
If .Selected(0) = True Then
For i = 1 To lstVintage.ListCount - i
.Selected(i) = False
Next i
End If
End With

End Sub

 

[Andrew Poulsom]

The Click event won't fire if MultiSelect is True. Use the Change event. You can disable events like this:

Suppressing Events In UserForms

I know it says in UserForms, but the same principle can be applied to controls on a worksheet.

 

[dinamike1]

Thanks. I just read through the suppression post and put an application.enableevents = false ahead of my for loop and then ... = true after it. It still didn't work. When I stepped through the code, the change event re-fired as soon as i updated the .selected(i) value to false. Am I doing this wrong? Following is the new code. FYI, i think i can work around this by counting the number of selected items ahead of time and persisting the increment value as I loop thru and re-fire the change event each time. something like "for i = lastgoodincrement+1 to lst***.listcount-1" and then close out the full routine once i have changed the right number of values. But this sure seems inefficient for something so simple. Thanks again for your help on this.

Private Sub lstVintage_Change()
    
    Dim i As Integer
    
    With lstVintage
        If .Selected(0) = True Then
            Application.EnableEvents = False
            For i = 1 To .ListCount - 1
                If .Selected(i) = True Then .Selected(i) = False
            Next i
            Application.EnableEvents = True
        End If
    End With

End Sub

 

[erik.van.geit]

I must go, so didn't test.
Since you are using Listfillrange, this can influence the process: I've seen cases where trouble occured from that side.
So see if it works when you empty the listfillrange an populate the listbox using the initialize event or activate event.

 

[dinamike1]

Will try and report back later. Thanks very much for your help and quick replies.

 

[dinamike1]

Well, I de-linked the listboxes from ranges and added items via code and the .additem method. No luck with that one either. It is rather odd when I step through the code via F8...when the code reaches a line where it changes a selection, it fires the change event again despite the application.enableevents = false statement. If that's not odd enough, once the code completes it's run on the re-fired event, it returns to the next line of the original run and continues on. I have a few nested areas where a change may be made so it took me a minute to spot what's going on. Not sure if anyone has seen this before, but it is a first for me. Following is the code as i have it now. I added a section to track when the "All" item selected during the prior change event otherwise once "All" is clicked first time, the user could never select anything else...the code would always de-select them.

Private Sub lstVintage_Change()
    
    Dim i As Integer
    Dim BoxType As String
    
'Sets box type
    BoxType = "Vintage"
    
    With lstVintage
        If .Selected(0) = True Then
                     
        'Disables change event
            Application.EnableEvents = False
            
        'De-selects 'All' when it was already selected the one prior
            If lSht.Range("IsAll" & BoxType).Value = True Then
                
                .Selected(0) = False
                lSht.Range("IsAll" & BoxType).Value = False
                
        'De-selects all other selections and sets IsAll tracker for next time
            Else
                lSht.Range("IsAll" & BoxType).Value = True
                For i = 1 To .ListCount - 1
                    If .Selected(i) = True Then .Selected(i) = False
                Next i
                
            End If
        
        'Enables change event
            Application.EnableEvents = True
        
        End If
    End With

End Sub

 

[Jaafar Tribak]

You don't use Application.EnableEvents to supress activeX controls events .... Read the second and third code sections in the link posted by Andrew .

 

[erik.van.geit]

This works for me:

Option Explicit

Dim Changing_lstVintage As Boolean

Private Sub lstVintage_Change()
 Dim i As Integer
 
If Changing_lstVintage Then Exit Sub
Changing_lstVintage = True

 With lstVintage
    If .Selected(0) = True Then
       For i = 1 To lstVintage.ListCount - 1
       .Selected(i) = False
       Next i
    End If
 End With

Changing_lstVintage = False

End Sub