View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
arjen van... arjen van... is offline
external usenet poster
 
Posts: 32
Default 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.