How to select multiple sheets in VBA?

[hideki]

Hi all,

I'm trying to create a macro/program that when execute will:

1. Ask user to select and open an Excel file.
--archieved this by using GetOpenFile method.
2. Ask user to select multiple sheets in the opened file.
--GOT STUCK here.
3. Copy some infos from the selected sheets to other sheet in othe
file.

I'm stuck in 2. How do I ask user to select multiple sheets?

I tried to open the file and loop through th
Activewindow.Selectedsheet, but only one sheet was selected. There i
no dialog to temporarily stop the macro and ask user to select th
sheets. Any idea how can I solve this

 

[Norman Jones]

Hi Hideki,

One way:

'=============>>
Sub Tester()
Dim strInput As String
Dim arr As Variant
Dim i As Long
Dim SH As Worksheet
Dim blOK As Boolean

blOK = True

strInput = InputBox(Prompt:="Please enter sheet " _
& "names separated with a comma" _
& vbNewLine & "e.g.:" _
& "Sheet1, Sheet3,Sheet5", _
Default:="Sheet1")

If StrPtr(strInput) = 0 Then
MsgBox "You pressed Cancel"
Exit Sub
Else
If Len(strInput) = 0 Then
MsgBox "OK was pressed but no entry was made."
Exit Sub
Else
arr = Split(strInput, ",")
For i = LBound(arr) To UBound(arr)
Sheets((arr(i))).Select blOK
blOK = False
Next i
End If
End If

'To demonstrate selection:
For Each SH In ActiveWorkbook.Windows(1).SelectedSheets
MsgBox SH.Name
Next

End Sub
'<<=============

 

[hideki]

Thank you very much Norman. The code works great. This is most likel
what I'm looking for.

Only one drawback is that if I could, I would like the user to selec
the sheets instead of write down the sheet names in the inputbox. Th
best is if the user could click the sheets tab (with Ctrl or Shift key
to make the selection. If that's impossible, may be I could try the use
form with sheet names populate on it and locate a checkbox for the use
to check. But it may take me years to figure out that myself

 

[Norman Jones]

Hi Hideki,

I could try the user
form with sheet names populate on it and locate a checkbox for the user
to check. But it may take me years to figure out that myself.

See John Walkenbach's approach at:

http://www.j-walk.com/ss/excel/tips/tip48.htm

You may be able to emply a simmilar approach.

 

[hideki]

Thank you very much, Norman. The link that you gave really help me. B
refering to it, I'd already accomplished the task. Really appreciat
your help.

hideki