I have a workbook that contains 25 worksheets. Suppose I select sheets 12,
15, and 21
Is there a way - programmatically - to cycle thru the sheets collection and
identify which ones have been selected?

I can count the number of sheets selected with:
SelectedSheets = ActiveWindow.SelectedSheets.Count

but I can't seem to find where I can read which ones are selected.

Glen

Try this if you only select worksheets and not chart sheets

Sub test()
Dim sh As Worksheet
For Each sh In ActiveWindow.SelectedSheets
MsgBox sh.Name
Next
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Glen Mettler" wrote in message ...
I have a workbook that contains 25 worksheets. Suppose I select sheets 12, 15, and 21
Is there a way - programmatically - to cycle thru the sheets collection and identify which ones have been selected?

I can count the number of sheets selected with:
SelectedSheets = ActiveWindow.SelectedSheets.Count

but I can't seem to find where I can read which ones are selected.

Glen

for each sh in ActiveWindow.SelectedSheets
msgbox sh.name
Next

--
Regards,
Tom Ogilvy


"Glen Mettler" wrote in message
...
I have a workbook that contains 25 worksheets. Suppose I select sheets
12,
15, and 21
Is there a way - programmatically - to cycle thru the sheets collection
and
identify which ones have been selected?

I can count the number of sheets selected with:
SelectedSheets = ActiveWindow.SelectedSheets.Count

but I can't seem to find where I can read which ones are selected.

Glen

"Glen Mettler" wrote in message
...
I have a workbook that contains 25 worksheets. Suppose I select sheets 12,
15, and 21
Is there a way - programmatically - to cycle thru the sheets collection
and identify which ones have been selected?

I can count the number of sheets selected with:
SelectedSheets = ActiveWindow.SelectedSheets.Count

but I can't seem to find where I can read which ones are selected.

Hi Glen,

I'm not totally sure I understand what you're looking for, but
ActiveWindow.SelectedSheets returns a collection of the selected Sheet
objects. You can enumerate this collection like so:

Sub PrintSelectedSheetNames()
Dim objSheet As Object
For Each objSheet In ActiveWindow.SelectedSheets
''' Print sheet name to Immediate window.
Debug.Print objSheet.Name
Next objSheet
End Sub

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

Glen

You could use code to loop through the selected sheets collection

Sub findSelectedSheets()
Dim wks As Worksheet
If ActiveWindow.SelectedSheets.Count = 1 Then
MsgBox "You do not have any grouped worksheets"
Exit Sub
End If
For Each wks In ActiveWindow.SelectedSheets
MsgBox "Worksheet named: " & wks.Name & _
" is part of a group"
Next wks
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Glen Mettler" wrote in message
...
I have a workbook that contains 25 worksheets. Suppose I select sheets 12,
15, and 21
Is there a way - programmatically - to cycle thru the sheets collection
and identify which ones have been selected?

I can count the number of sheets selected with:
SelectedSheets = ActiveWindow.SelectedSheets.Count

but I can't seem to find where I can read which ones are selected.

Glen

The following works for me:

Dim Sh As Object
For Each Sh In ActiveWindow.SelectedSheets
Debug.Print Sh.Name
Next Sh

On Mon, 21 Feb 2005 14:30:20 -0600, "Glen Mettler"
wrote:

I have a workbook that contains 25 worksheets. Suppose I select sheets 12,
15, and 21
Is there a way - programmatically - to cycle thru the sheets collection and
identify which ones have been selected?

I can count the number of sheets selected with:
SelectedSheets = ActiveWindow.SelectedSheets.Count

but I can't seem to find where I can read which ones are selected.

Glen

Just what I needed. Thanks

Glen

"Ron de Bruin" wrote in message
...
Try this if you only select worksheets and not chart sheets

Sub test()
Dim sh As Worksheet
For Each sh In ActiveWindow.SelectedSheets
MsgBox sh.Name
Next
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Glen Mettler" wrote in message
...
I have a workbook that contains 25 worksheets. Suppose I select sheets
12, 15, and 21
Is there a way - programmatically - to cycle thru the sheets collection
and identify which ones have been selected?

I can count the number of sheets selected with:
SelectedSheets = ActiveWindow.SelectedSheets.Count

but I can't seem to find where I can read which ones are selected.

Glen