Select one or more worksheets from a listbox

[Budget Programmer]

Hello,

I want to present a dialog box (I assume a ListBox) to the user. That
dialog box should contain a listing of all the worksheets in the workbook.
I'd like the user to select one or more worksheets. How would I construct
this listbox? I have experince with Excel Macro's, but haven't used
listboxes before.
Many Thanks.

 

[p45cal]

see attached.
Note the code in -Userform1 -code module and that the Listbox'
*Multiselect *property is set to -1 - fmMultiSelectMulti

+-------------------------------------------------------------------
|Filename: SelectWorksheetsInListBox.xls
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=227
+-------------------------------------------------------------------

 

[arjen van...]

I did something pretty similar recently. In my case I allowed the user to
print multiple sheets to print. After adding the listbox to the userform, go
to the listbox properties and set MultiSelect = 1 - fmMultiSelectMulti.

Then add the following code to the userform module:

Option Explicit
Private Sub UserForm_Initialize()

Dim SheetCount As Integer
SheetCount = ThisWorkbook.Sheets.Count

Dim i As Integer

For i = 1 To SheetCount
lstSheets.AddItem (Sheets(i).Name)
Next i

End Sub
Private Sub btnPrint_Click()

Application.ScreenUpdating = False

Dim k As Integer

With lstSheets
For k = 0 To .ListCount - 1
If .Selected(k) Then
Sheets(k + 1).PrintOut
End If
Next k
End With

Unload UserForm1

End Sub

And in a general module I have a simple button click event to show the form:

Sub CallPrint()

UserForm1.Show

End Sub

Note: I haven't used default names for the listbox & print button in my
example. But it's simple to just change the name property.

 

[Budget Programmer]

Hi Arjen,
Thanks for the reply. Where does lstSheets come in? I tried declaring it
as an object and as a worksheet. Thanks for your help.
Regards,
Phil

 

[arjen van...]

Hi Phil,

lstSheets is actually the name I gave the ListBox, instead of the default
name (which would be ListBox1). So there's no need to declare anything for
it. Likewise btnPrint is just the CommandButton with a name change.

Hopefully that clarifies things a bit.

 

[Budget Programmer]

Should have guessed that. Thanks.