VBA listbox selections disappear in the listbox during calcu

[mphansen]

I have a VBA listbox that seems to reset the selections within the listbox whenever calculations are performed on the sheet, or when I open the sheet. That is to say, previous items selected clear from the box when unrelated calucations run.

Also, when I add items to the defined list of the listbox, they don't show up in the listbox ?

Is there anything faulty in my code below:


Private Sub lb1_Change()
Dim i, j As Integer
j = 1
Sheets("Settings").Range("results").Value = ""
With lb1
For i = 0 To .ListCount - 1
If .Selected(i) Then
Sheets("Settings").Range("results").Cells(j, 1).Value = lb1.List(i)
j = j + 1
End If
Next i
End With
End Sub


Thanks - Mark

 

[Mitchman]

Question... Does it clear in the active sheet but remain correct if printed or "Print Preview"?

 

[mphansen]

Mitchman - The listbox (VBA control toolbox)only clears temporarily when the workbook opens or if any other calculation is performed on the sheet. It then populates the listbox again after the sheet is done calculating.

The listbox is populated off a dynamic defined range - that is based on a certain selection elsewhere on the sheet, so usually 8 names are displayed in the listbox. If one of the selections is deselected, the dynamic defined range will change to 7 names
which then appear in the listbox.

The results of the listbox selections occur in another range using J = J + 1 code.

Sometimes this same listbox magically expands (grows larger) after the workbook calculates --- I don't know why this happens

So to answer your question, it only clears temporarily or "resets." The listbox contents appear in printed pages.

Greatly appreciate any answer from Mitchman or anyone else