ListBox Click Event

[BHatMJ]

I have a listbox control on a user form in Excel. I want to add code
whenever an item in the listbox is selected/unselected. I assumed I could do
this in the "click" event of the list box. However, clicking on any of the
items in the listbox does not trigger the event. Can anyone help?

 

[Steve]

xyz = Activesheet.ListBox1. Value
would put the selection into variable xyz for use in your code

 

[BHatMJ]

The control is on a userform dialog box. It seems as if it should be
straight forward. Below is a code example:

Private Sub listbox1_Click()
'if any items in listbox1 are not selected, set "select all" checkbox to
false
For i = 0 to listbox1.ListCount - 1
If listbox1.Selected(i) = False Then
checkbox_all.Value = False
End If
Next
End Sub

 

[Dave Peterson]

Maybe using ListBox1_Change would work better.

The control is on a userform dialog box. It seems as if it should be
straight forward. Below is a code example:

Private Sub listbox1_Click()
'if any items in listbox1 are not selected, set "select all" checkbox to
false
For i = 0 to listbox1.ListCount - 1
If listbox1.Selected(i) = False Then
checkbox_all.Value = False
End If
Next
End Sub

 

[BHatMJ]

I attempted that but the checkbox and the listbox are dependent on each
other. Every time the check box is clicked it changes the selection status
in the lsitbox and triggers the listbox change event. On the other hand,
when the listbox item is selected, it triggers the click event of the
checkbox. It needs to be the Click event.

Here is the situation (should be simple, right?):

checkbox for "Select All Items" in listbox
listbox items - if all items are NOT selected, checkbox should be false

 

[Dave Peterson]

Since you're really only using the checkbox to cause something to happen--not to
indicate a yes/no, on/off, 1/0 situation, how about an alternative?

How about using a couple of commandbuttons instead?

You'll find that the code is easier and as a user, I _think_ I've seen more
buttons do the "select all/Unselect All" processing than checkboxes.

Anyway, I put 3 commandbuttons on a userform and a little listbox.

This was the code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim iCtr As Long
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
.Selected(iCtr) = True
Next iCtr
End With
End Sub
Private Sub CommandButton3_Click()
Dim iCtr As Long
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
.Selected(iCtr) = False
Next iCtr
End With
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long

With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
For iCtr = 1 To 5
.AddItem "A" & iCtr
Next iCtr
End With

Me.CommandButton1.Caption = "Cancel"
Me.CommandButton2.Caption = "Select All"
Me.CommandButton3.Caption = "Unselect All"

End Sub

 

[BHatMJ]

Works for me -- thanks!

Since you're really only using the checkbox to cause something to happen--not to
indicate a yes/no, on/off, 1/0 situation, how about an alternative?

How about using a couple of commandbuttons instead?

You'll find that the code is easier and as a user, I _think_ I've seen more
buttons do the "select all/Unselect All" processing than checkboxes.

Anyway, I put 3 commandbuttons on a userform and a little listbox.

This was the code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim iCtr As Long
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
.Selected(iCtr) = True
Next iCtr
End With
End Sub
Private Sub CommandButton3_Click()
Dim iCtr As Long
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
.Selected(iCtr) = False
Next iCtr
End With
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long

With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
For iCtr = 1 To 5
.AddItem "A" & iCtr
Next iCtr
End With

Me.CommandButton1.Caption = "Cancel"
Me.CommandButton2.Caption = "Select All"
Me.CommandButton3.Caption = "Unselect All"

End Sub