Listbox question

[dave8]

I have a listbox on a worksheet. The properties on this listbox is [1-fmMultiSelectMulti] and [1-fmListStyleOption]. So just to clarify, the items on the listbox is such that there are checkboxes besides the list of items. In the Listbox1_Change procedure, I have code to loop thru Listbox1.selected(i). So, if an item is selected, I have code to do something. But, I also want to do something if an item is de-selected, so to speak. Is there a way to do this??

 

[p45cal]

On cursory testing this seemed to work:

Private Sub ListBox1_Change()
Static LastSel()
Dim SelNow()
ReDim SelNow(0 To Me.ListBox1.ListCount - 1)
Set ccc = Me.ListBox1
For i = 0 To Me.ListBox1.ListCount - 1
  SelNow(i) = Me.ListBox1.Selected(i)
  If SelNow(i) <> LastSel(i) Then
    MsgBox Me.ListBox1.List(i) & " has been " & IIf(SelNow(i), "", "de-") & "selected"
  End If
Next i
LastSel() = SelNow()
End Sub

 

[p45cal]

addendum:
Discovered a problem when LastSel array is not initialized. Here I initialize it on the worksheet being activated - it doesn't have to be there, it could be on some other event:

Dim EstablishLastSel As Boolean

Private Sub ListBox1_Change()
Static LastSel()
Dim SelNow()
If EstablishLastSel Then
ReDim LastSel(0 To Me.ListBox1.ListCount - 1)
  For i = 0 To Me.ListBox1.ListCount - 1
    LastSel(i) = Me.ListBox1.Selected(i)
  Next i
  EstablishLastSel = False
Else
  ReDim SelNow(0 To Me.ListBox1.ListCount - 1)
  Set ccc = Me.ListBox1
  For i = 0 To Me.ListBox1.ListCount - 1
    SelNow(i) = Me.ListBox1.Selected(i)
    If SelNow(i) <> LastSel(i) Then
      MsgBox Me.ListBox1.List(i) & " has been " & IIf(SelNow(i), "", "de-") & "selected"
    End If
  Next i
  LastSel() = SelNow()
End If
End Sub

Private Sub Worksheet_Activate()
EstablishLastSel = True
ListBox1_Change
End Sub

ps. LastSel = Last Selection
SelNow = Selected Now

 

[dave8]

First, thanks for taking a moment to think of solution. I can't get this to work. I'm getting a subscript out of range error at ' If SelNow(i) <> LastSel(i) Then ' line.

 

[p45cal]

First, thanks for taking a moment to think of solution. I can't get this to work. I'm getting a subscript out of range error at ' If SelNow(i) <> LastSel(i) Then ' line.

In the error box choose End.
Then select another sheet, reselect the listbox sheet and try again. It's the business of establishing LastSel() that I mentioned.

Also make sure that the line:
Dim EstablishLastSel As Boolean
is above all other subs/functions in the module.

 

[dave8]

I got pass the error but it's never displaying the Msgbox. I select/de-select from the listbox.

 

[p45cal]

Well I tested it here and it works.
It's likley that you may have some different arrangement from mine (1, single column Listbox on a sheet and all the code in that sheet's code module. If you want, post a copy of the workbook on the internet, say at box.net, or send me a copy privately (Private Message me here at mrexcel with a request for my email address) and I will try to establish what's going wrong.

 

[dave8]

Firstly, the property on my listbox is not set as single. It's set as fmMultiSelectMulti and fmListStyleOption. My workbook is about 2mb, but the section of code is handled the same you provided me. Another note is that my listbox is on a worksheet and not on a userform.

 

[p45cal]

Firstly, the property on my listbox is not set as single. It's set as fmMultiSelectMulti and fmListStyleOption.

Yep, so's mine. I said it was single column.

My workbook is about 2mb, but the section of code is handled the same you provided me.

Fine.

Another note is that my listbox is on a worksheet and not on a userform.

Mine's on a worksheet too.

If it bugs out press End, switch away, then back, to the sheet.
To be certain the code's not in break mode after exploring with Debug, click the reset button (or Run|Reset in the VBE). Switch away and back to the sheet and try again.
Of course, ensure that macros are enabled.

 

[dave8]

I created a separate workbook with your code. Can I send it to you so you can look at what I'm doing incorrectly? It's still not displaying the msgbox. Although if I run it in debug mode, it works fine. That is, execute each statement line by line.