Selected Sheets

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

here is some simple code that cycles through all selected sheets and prints the name of each sheet:

Sub IDSelectedSheets()

Dim xlsheet As Worksheet

For Each xlsheet In ActiveWindow.SelectedSheets
MsgBox xlsheet.Name
Next xlsheet

End Sub


If you like to use more formal indices (as I do), then you might try this:

Sub IDSelectedSheets2()

Dim I As Integer

For I = 1 To ActiveWindow.SelectedSheets.Count
MsgBox ActiveWindow.SelectedSheets(I).Name
Next I

End Sub

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" <[email protected]> wrote in message news:[email protected]...
>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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
>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
[email protected]HIS


"Glen Mettler" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]>
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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>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
>>
>>
>
>