VBA listbox selections disappear in the listbox during calcu

mphansen

New Member
Joined
Jun 23, 2002
Messages
45
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Mitchman

New Member
Joined
Oct 8, 2002
Messages
38
Question... Does it clear in the active sheet but remain correct if printed or "Print Preview"?
 

mphansen

New Member
Joined
Jun 23, 2002
Messages
45
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
 

Forum statistics

Threads
1,144,254
Messages
5,723,269
Members
422,489
Latest member
MyForcey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top