Help with Form List Box

[jim_zoeller]

I have a list box which is populated from a secondary worksheet.
However, I want only the items selected in the list box to print when
the worksheet is printed. Any ideas?

 

[Dave Peterson]

How can you be sure that the number of selected items will be visible in the
listbox?

If you're sure that they'll fit, can you delete the unselected items?

If yes, then I put a listbox from the Forms toolbar on a worksheet. I populated
it with a range from that other worksheet.

Then cleaned it up, printed it (I used preview:=true for testing), then
repopulated the listbox.


Option Explicit
Sub loadListBox()
Dim LB As ListBox
Set LB = Worksheets("sheet2").ListBoxes("list box 1")
LB.List = Worksheets("sheet1").Range("a1:a20").Value
LB.MultiSelect = xlSimple
End Sub
Sub CleanUpListBox()

Dim LB As ListBox
Dim iCtr As Long
Dim mySelected() As Long

Set LB = ActiveSheet.ListBoxes("list box 1")

ReDim mySelected(1 To LB.ListCount)

For iCtr = LB.ListCount To 1 Step -1
mySelected(iCtr) = LB.Selected(iCtr)
If LB.Selected(iCtr) Then
'keep it
Else
LB.RemoveItem iCtr
End If
Next iCtr

ActiveSheet.PrintOut preview:=True

Call loadListBox

For iCtr = LB.ListCount To 1 Step -1
LB.Selected(iCtr) = mySelected(iCtr)
Next iCtr

End Sub

========
If you use a listbox from the Control Toolbox toolbar, the code is different.

Option Explicit
Sub loadListBox()
Dim LB As msforms.ListBox
Set LB = Worksheets("sheet2").ListBox1
LB.List = Worksheets("sheet1").Range("a1:a20").Value
LB.MultiSelect = fmMultiSelectMulti
End Sub
Sub CleanUpListBox()

Dim LB As msforms.ListBox
Dim iCtr As Long
Dim mySelected() As Long

Set LB = ActiveSheet.ListBox1

ReDim mySelected(0 To LB.ListCount - 1)

For iCtr = LB.ListCount - 1 To 0 Step -1
mySelected(iCtr) = LB.Selected(iCtr)
If LB.Selected(iCtr) Then
'keep it
Else
LB.RemoveItem iCtr
End If
Next iCtr

ActiveSheet.PrintOut preview:=True

Call loadListBox

For iCtr = LB.ListCount - 1 To 0 Step -1
LB.Selected(iCtr) = mySelected(iCtr)
Next iCtr

End Sub

=========
I added a button from the forms toolbar to the worksheet. I assigned the
CleanUpListBox() macro to that button.

The loadlistbox can be run from the auto_open procedure??

Option explicit
Sub Auto_open()
call loadListBox
end sub