listbox selections clear prematurely

[Ken Cowen]

I have a listbox on a userform. The listbox has a row source that comes from sheet5 (about 300 items). The following code loops through the listbox data, and if the row is selected it writes the value of the listbox to a range on another worksheet (sheet2, which is initially blank). I am really sure this was working fine last week, but now as soon as the first selected row is written to the worksheet the listbox selections are all cleared; so only the first value is entered on the worksheet. It is entered correctly. This happens whether or not the userform is shown modal or not. Another aspect that has me thinking I am not crazy is that this morning the VBE toolbox looks different. The tools are in color and the icons are different; mostly there is just a little color added, but some are very small, and the commandbutton icon is hardly recognizable. The code that worked last week, but now clears the listbox selections as soon as the first selected item is written tot he worksheet is:

Private Sub CommandButton6_Click()


Dim n As Integer, i As Integer

n = 1

For i = 0 To ListBox3.ListCount - 1
If ListBox3.Selected(i) = True Then
R(n, 1).Value = ListBox3.List(i, 0)
n = n + 1
End If
Next i


End Sub

R is a public range variable on a different sheet than the rowsource for listbox3. When I step through this code it works fine up to the point where R(N,1) is changed to the selected listbox list item. As soon as that shows up on the worksheet, all selections are cleared. Any ideas would be appreciated.

Thanks

Ken

 

[Ken Cowen]

I figured it out. I had added a column to the listbox rowsource that contains a formula. Apparently simply calculating the workbook triggers resetting the listbox selections if they come from a rowsource. The formula is completely internal to the rowsource data; but it is enough to trigger the clearing. I guess I did not notice the problem until too long after I made that change to associate it with the issue; also I was kind of hung up on the different looking toolbox icons this morning.

Setting application.calculation to manual before the loop runs, and back to automatic on completion takes care of the problem.


Thanks for any consideration anyone gave to my post. This site is a great resource.

Ken