Multi Select List Box

[jacqui]

I have a user form containing a multi select list box
based on names of worksheets in a workbook (but not all of
them mind). I have written the code when I need to work
on all of the selected worksheets at once, ie
ThisWorkbook.Sheets(aSheets).Select
However what would be the correct syntax (I'm guessing at
For Each), to select the chosen sheets one at a time, then
loop to the next one. You've probably guessed that
there's some stuff going on in between the loop but I'm ok
with the VBA for that.

I've tried
For Each Worksheets.(asheets) in This Workbook but excel
didn't like that. It's pitiful I know, so pls don't laugh.

Any help is appreciated
Thanks, Jacqui

FYI
The code is as follow
Dim i As Integer
Dim aSheets() As String
Dim lListItem As Long
Dim lIndex As Long

lIndex = -1

Application.ScreenUpdating = False


For lListItem = 0 To LstSheet.ListCount - 1
If LstSheet.Selected(lListItem) Then
lIndex = lIndex + 1
ReDim Preserve aSheets(0 To lIndex)
aSheets(lIndex) = LstSheet.List(lListItem)
End If
Next lListItem

there's lots of other code in here but I won't bore you
with that

ElseIf OptPrint.Value = True And lIndex > -1 Then

Set_Print_Areas

this is where I need some help!

 

[Patrick Molloy]

take a look at the following which should guide you...

Option ExplicitPrivate Sub cmdProcess_Sheets_Click()
Dim ws As Worksheet
Dim i As Long
With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
Set ws = Worksheets(.List(i))
ProcessSheet ws

End If
Next
End Sub
Private Sub UserForm_Initialize()
Dim ws As Worksheet
With ListBox1
For Each ws In ThisWorkbook.Worksheets
.AddItem ws.Name
Next
End With
End Sub

On startup, the initialisation method simply populates a
listbox with sheet names. A button named
cmdProcess_Sheets , when clicked, loops throught he
listbox, and call ProceSheets method for any items on the
listbox that are selected.

HTH
Patrick Molloy
Microsoft Excel MVP