Selected Sheets

[Glen Mettler]

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

 

[Ron de Bruin]

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

 

[Tom Ogilvy]

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

 

[Rob Bovey]

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

 

[Nick Hodge]

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
(e-mail address removed)

 

[Myrna Larson]

The following works for me:

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

 

[Glen Mettler]

Just what I needed. Thanks

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