MultiSelect ListBox losing its check marks

[naznorb]

I have a MultiSelect ListBox (ActiveX) applied directly to a worksheet (liststyle Option -- the checkbox version). I am using the ListBox_Change event to trigger a sub that moves the selected items to a list on another worksheet. So far so good. (see code)

Private Sub ListBox1_Change()
Dim i As Integer, li As Integer

i = 1
With ListBox1
For li = 0 To .ListCount - 1
If .Selected(li) = True Then
Worksheets("lists").Cells(10, 10).Offset(i, 0) = .List(li)
i = i + 1
End If
Next li
End With
Worksheets("lists").Cells(10, 11) = i - 1
End Sub

A half second or so after the selections are made, they are automatically cleared. They've been transferred to the appropriate place, so that's good -- but from the user's perspective, I'd like them to remain checked.

I'm using Excel 2007 on Windows 7.

I very much hope this is something obvious / stupid like I should be using a different event. I'm a relative newbie, so be gentle!

Thanks in advance.

 

[naznorb]

To be clear, I'm losing my check marks on calculate. A very thorough search of various forums turns up numerous people having the opposite problem (i.e., needing to clear selected items), but none having my problem.

 

[Domenic]

I'm using Excel 2010 on Windows 7, and I cannot re-create your problem. Each time I select an item from the ListBox, it's written to the worksheet and the item remains selected. Do you have any other code that might be de-selecting the items? Maybe within some calculate event handler?

 

[Domenic]

Try checking the code modules for your sheet and workbook. In the Visual Basic Editor (Alt+F11), bring up the Project Explorer (Ctrl+R), and double-click first the icon for your sheet, and then the icon for your workbook. Any code within their modules?

 

[naznorb]

Thanks for your input, Dominic. I've checked the ThisWorkbook and the relevant sheet and the only other code in there is an on-open sub that just defines some options for use by other macros. But there's nothing on calculate that I can see anywhere.

I must admit, I'm deeply mystified. There must be SOMETHING going on, though -- Just to be sure I tried the same code in a brand new workbook and I don't experience the same problem.... back to the salt mine, I guess.

Thanks for your help.

 

[naznorb]

Just for fun I tried removing ALL code from the same workbook except for the sub in question ... and I STILL lose the checkmarks.

 

[Domenic]

Just for fun I tried removing ALL code from the same workbook except for the sub in question ... and I STILL lose the checkmarks.

With regards to the code you posted, was that the entire code? Or are there other lines of code?

 

[naznorb]

That is the entire code for the control, and unless I'm really a dope (which, alas, may be true), is now the only code in the workbook. I feel that CAN'T be the case, since when I create a fresh workbook and use the same code for the control, I don't experience this problem. But I'm out of ideas about where to look. I've opened every module in the project (and all the pages, and "this workbook") and cleared every bit of code I can detect.

Thanks for your help -- it is much appreciated.

 

[jwsmith708]

I am having the very same problem (with my ListBox choices being cleared after the Worksheet Calculates) using this code:

Private Sub ListBox1_Change()
Dim i As Integer, li As Integer

i = 1
With ListBox1
For li = 0 To .ListCount - 1
If .Selected(li) = True Then
Worksheets("lists").Cells(1, 10).Offset(i, 0) = .List(li)
i = i + 1
End If

Next li
End With
Worksheets("lists").Cells(2, 11) = i - 1
End Sub

Like the OP, this is the only code in the Workbook, and like the OP, I can use the same code in a different Workbook, and the Listbox choices are NOT cleared.

Hope someone has some ideas here.

Jeff

 

[naznorb]

Great minds think alike, Jeff. Cheers -- James. (check out my username, backwards.)