Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Programmatically detecting grouped sheets (2000, SR-1)

    I have a situation where users select multiple sheets in a workbook (i.e., group the sheets) and then invoke a macro. The macro needs to do the equivalent of "For each selected sheet". Does anybody know how to do this?

  2. #2
    Lounger
    Join Date
    May 2001
    Location
    the Netherlands
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Programmatically detecting grouped sheets (2000, SR-1)

    Hi Colin,

    A quick experiment turned out something like:
    <pre>Dim sh As Worksheet

    Sheets(Array("Sheet1", "Sheet3")).Select

    For Each sh In Workbooks("BOOK1.XLS").Windows(1).SelectedSheets
    MsgBox sh.Name & " is selected"
    Next
    </pre>

    Hope this gets you started,


    Ingrid

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Programmatically detecting grouped sheets (2000, SR-1)

    Ingrid - thanks a lot. I'd been looking for something like that, but was looking under the workbook object rather than the windows object (which makes a lot more sense since grouped sheets relate to a view of the workbook, not the workbook itself).

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Programmatically detecting grouped sheets (2000, SR-1)

    Then again, I just read Legare's post - .SelectedSheets is also valid with the workbook object...

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Programmatically detecting grouped sheets (2000, SR-1)

    <pre>Dim oCurSheet as Worksheet
    For Each oCurSheet in ActiveWindow.SelectedSheets
    MsgBox oCurSheet.Name & " is selected."
    Next oCurSheet
    </pre>

    Legare Coleman

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Programmatically detecting grouped sheets (2000, SR-1)

    Sorry about that. That's what I get for typing directly into a reply rather than trying it first. That should have been ActiveWindow, not ActiveWorkbook. I have corrected my previous post.
    Legare Coleman

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Programmatically detecting grouped sheets (2000, SR-1)

    Thanks Legare - I'd just discovered the same thing...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •