Entering values into a ListBox.

[fishonspeed]

I am trying to set up a multi-select ListBox on a form that will list all of
the sheets in a workbook. I can get all the sheet names picked up, I just
can't figure out how to place them into the ListBox.

 

[Dave Peterson]

Like in a userform?

I posted this yesterday:

Instead of using checkboxes, you could use a listbox and make that listbox's
style show checkboxes.

I built a small userform (a listbox and two commandbuttons).

This is the code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim iCtr As Long
Dim sCtr As Long
Dim mySheetNames() As String

ReDim mySheetNames(1 To ActiveWorkbook.Sheets.Count)
sCtr = 0
With Me.ListBox1
For iCtr = 1 To .ListCount
If .Selected(iCtr - 1) Then
sCtr = sCtr + 1
mySheetNames(sCtr) = .List(iCtr - 1)
End If
Next iCtr
End With

If iCtr = 0 Then
MsgBox "No Sheets Selected"
Else
ReDim Preserve mySheetNames(1 To sCtr)
Me.Hide
ActiveWorkbook.Sheets(mySheetNames).PrintOut preview:=True
End If

Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long

With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ListStyle = fmListStyleOption
End With

For iCtr = 1 To ActiveWorkbook.Sheets.Count
If Sheets(iCtr).Visible = xlSheetVisible Then
Me.ListBox1.AddItem Sheets(iCtr).Name
End If
Next iCtr

With Me.CommandButton1
.Caption = "Cancel"
.Cancel = True
End With

With Me.CommandButton2
.Caption = "Ok"
.Default = True
End With

Me.Caption = "Select Sheets to Print"

End Sub