Select one or more worksheets from a listbox
 

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.
--
Programmer on Budget

Select one or more worksheets from a listbox
 

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


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

--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126442

Select one or more worksheets from a listbox
 

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.

Select one or more worksheets from a listbox
 

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
--
Programmer on Budget


"arjen van..." wrote:

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.

Select one or more worksheets from a listbox
 

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.

Select one or more worksheets from a listbox
 

Should have guessed that. Thanks.
--
Programmer on Budget


"arjen van..." wrote:


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.