In VBA : testing if a sheet is within a selected group

[Gustaw]

Issue :
How can I test a sheet / tab whether it belongs to the selected / grouped sheets or not ?

This is my first post here but I have used info posted on this forum before.
So big thank you to all the community.

I admit I have not spent much time trying to find a solution to my problem.
I hope someone may have an "instant" answer at hand.

I want to perform certain action on each of the sheets that are currently grouped within a workbook.
These can be selected more or less randomly so do not assume any logic in terms of selection. There might be all of the sheets, there might be just one ...

I need VBA to cycle through all selected tabs "doing something".
It might be beneficial if the selected sheets remain selected after the process finishes but :
- it is not essential, only beneficial
- the "process" may actually deselect individual sheets or ungroup them altogether

I thought that in first step I may need to create an array of "markers" for all the sheets within a workbook to store info about which sheets are grouped / selected and which are not.

Then I can cycle through all the sheets and perform the "processing" on those that are marked as grouped.

The end action could be to recreate the group if the process ungroups the sheets but, as i wrote, this is a "nice-to-have" requirement.

I think I have come across an Excel "object" that represents grouped sheets but I do not want to cycle through its members in case the "processing" may ungroup the sheets.

I thought it would be prudent to store info about which sheets are groupped and which are not upfront before any "processing" commences.
Then I could use this info for "conditional" processing ... if grouped to do it ... if not grouped do not do it ...

How can I test a sheet / tab whether it belongs to the selected / grouped sheets or not ?
Activesheet seems to point to a single sheets that, presumably, is "on top of the stack" and actually displayed on screen (???).

What is the sheet property I should test ?

There is an action .Select but I have not come across a property .Selected or .Grouped ... I think.

Regards

 

[RoryA]

A simple loop:

Dim sht as Object
for each sht in Activewindow.SelectedSheets
' in reality you would process sht here
msgbox sht.name
next sht

 

[VoG]

The following approach should workprovided that you don't select or ungroup sheets within the code

Dim ws As Worksheet
For Each ws In ActiveWindow.SelectedSheets
'you code here
Next ws

 

[Gustaw]

Thanks.

OK, I will test it.
My concern was that if my code changes the group then

ActiveWindow.SelectedSheets

will change accordingly too.

But I can test whether this will happen or not.
I will report back

 

[RoryA]

It won't affect the looping but if you prefer you can store them to reinstate at the end:

    Dim sht                   As Object
    Dim oSheets               As Sheets
    Set oSheets = ActiveWindow.SelectedSheets
    For Each sht In oSheets
        ' in reality you would process sht here
        sht.Select True
        MsgBox sht.Name
    Next sht

 

[Gustaw]

It won't affect the looping but if you prefer you can store them to reinstate at the end:

    Dim sht                   As Object
    Dim oSheets               As Sheets
    Set oSheets = ActiveWindow.SelectedSheets
    For Each sht In oSheets
        ' in reality you would process sht here
        sht.Select True
        MsgBox sht.Name
    Next sht

RoryA, this is exactly what I tried to achieve.

As promised I did some testing (and missed to read your reply) and ended up with a very similar solution but, I admit, significantly less elegant than yours !


Here I am :

    Dim CollectioOfSelectedWorksheets As New Collection
    Dim ws As Worksheet

' Copy SelectedSheets collection

    For Each ws In ActiveWindow.SelectedSheets
        CollectioOfSelectedWorksheets.Add Item:=ws
    Next ws

' Select each worksheet from the group in turn and perform processing

    For Each ws In CollectioOfSelectedWorksheets
        ws.Select
' Processing code here
    Next ws

' End action - re-select the initial group of worksheets

    For Each ws In CollectioOfSelectedWorksheets
        ws.Select Replace:=False
    Next ws

It works !

The key difference between your solution and mine is the way how the SelectedSheets collection gets backed up. Your solution is much neater and, presumably, quicker in execution as you avoid iterating through all the collection members.

Many thanks !

 

[RoryA]

Glad to help, and welcome to the forum, by the way.