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.

 

[jwsmith708]

Ha Ha! I thought that might be you, James ... given we are both barking up the same tree at the same time!

Still searching!

Jeff

 

[naznorb]

The problem appears to be not in the code but in the fact that in the original version the control was populated using a dynamic named range created with an OFFSET. I think it recalculates the range and thus re-populates the control. Does this make sense to anyone following along at home?

 

[Domenic]

The problem appears to be not in the code but in the fact that in the original version the control was populated using a dynamic named range created with an OFFSET. I think it recalculates the range and thus re-populates the control. Does this make sense to anyone following along at home?

Yes. I've tested it and found that when data is removed from the dynamic named range, all items in the ListBox are unchecked. Also, each item in the ListBox that correspond to the data that was removed is blank, while the checkbox remains. Maybe you can use the List property to copy the values to the ListBox, and update it when necessary. So, assuming that your dynamic range is named "MyDynRange"...

UserForm1.ListBox1.List = Range("MyDynRange").Value

Then, run it again when you need to update the ListBox.

 

[naznorb]

Thanks Dominic. I don't think that's quite going to do the trick because what I'm concerned about is not that the checkmarks disappear when the contents of the OFFSET change but rather that the checkmarks disappear immediately after selection (on the first calculate) -- I presume because the OFFSET is being re-evaluated and the control is being repopulated. I think I may be stuck storing the selected values somewhere (an array, I guess) and setting the ".Selected" property manually each time the ListBox changes. Or at least, that's the best I can come up with. Any insights much appreciated!

 

[Domenic]

...the checkmarks disappear immediately after selection (on the first calculate)...

Actually, in my case, the checkmarks do not disappear immediately after selection.

...I think I may be stuck storing the selected values somewhere (an array, I guess) and setting the ".Selected" property manually each time the ListBox changes...

If the checkmarks disappear immediately after selection, will it be possible to store the selected values in an array?

 

[naznorb]

I think so. The checkmarks are disappearing on the first calculate after selections (which is a little tricky, since if you don't select fast enough, the sheet calculates and what you've selected disappears).

My Listbox_Change-triggered sub successfully reads what has been selected and transfers it to a new sheet, so I think all I need to do is add to my Listbox_Change sub the loading of the selected items into the array, and then create a Sheet_Calculate-triggered sub to just load them back into the "Selected" property.

Or at least, that's my hope. I plan to jump on this as soon as my machine is free from a somewhat lengthy install, and will report back.

By the way, I wonder if the solution proposed in this (very-closely-related) post (that I missed beforehand through inadequate searches) would work. The problem is, it's clearly a conversation between two skilled VBA-coders and I'm too inexperienced to understand what they mean by "convert to static range."

http://www.mrexcel.com/forum/showthread.php?t=499431

Really appreciate your help, Dominic.

 

[Domenic]

First, my apologies. I re-read your original post and realized that we're dealing with an ActiveX ListBox on your worksheet. For some reason, I thought we were dealing with a ListBox on a UserForm. Now that I've re-tested it, the ListBox behaves in exactly the same way as you've described. The selections get cleared once a calculation for the worksheet takes place. Therefore, as per the thread for which you've provided a link, you can use VBA to make it a fixed range. So, for example, if the name of your dynamic range is MyDynRange, you could use the following to set a static range for the ListFillRange property...

Worksheets("Sheet1").ListBox1.ListFillRange = Range("MyRange").Address

Change the worksheet name, accordingly.

 

[Domenic]

Also, if the dynamic named range is located in another sheet, let's say Sheet3...

Worksheets("Sheet1").ListBox1.ListFillRange = Worksheets("Sheet3").Range("MyDynRange").Address

Or, using the code names for the sheets...

Sheet1.ListBox1.ListFillRange = Sheet3.Range("MyDynRange").Address

 

[nkrivykh]

Had a similar issue in Excel 2010. Multi-select checkbox on a worksheet, in my case the range (ListFillRange) was not dynamic, it was like "Sheet1!A1:A100" (no formulas there just fixed values).
Still, the selections in my listbox were disappearing after the worksheet calculates.
Moreover, I noticed that I did not have this problem when there were not so many calculations, rather it appeared when I made a lot of calculations on the worksheet.
Was only able to solve the issue in the following way: I used AddItem or RemoveItem methods to add or remove items to the list (i.e. not using ListFillRange at all, keeping it blank).
This way the selections did not disappear.
Hope this may save someone's time if they have the same issue.