Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Grouping Sheets Dynamically (Excel 2000, SP2)

    I have a procedure that runs whenever a certain sheet in my workbook is selected. It groups a sub-set of worksheets. The problem I face is that the set of individual sheets that are grouped is dynamic depending on what the user has added or deleted. So, I set up a hidden sheet that just keeps track of all the worksheets that should be included in the grouping. When the first sheet is activated, the code steps through all the sheet names in column 1 of this hidden sheet (Sheet6 in the example) and builds a string. But when I pass the string to the Array function, I get a subscript out of range error. I'm obviously missing something, but I can't see what it is. Here is the code from the sample, in case you don't need the actual sheet to figure it out:

    Private Sub Worksheet_Activate()
    Dim Cell As Range
    Dim strSheets As String

    'Loop through the list of grouped sheets
    For Each Cell In Worksheets("Sheet6").Range("GroupedSheets")
    strSheets = strSheets & Chr(34) & Cell.Value & Chr(34) & ", "
    Next Cell

    'Get rid of the last comma
    strSheets = Left(strSheets, Len(strSheets) - 2)

    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">'The code that doesn't work
    Sheets(Array(strSheets)).Select</span hi>

    'The code I'm trying to automate
    Sheets(Array("Sheet1", "Sheet3", "Sheet5")).Select

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Grouping Sheets Dynamically (Excel 2000, SP2)

    Your array has only one element, consisting of the string "Sheet1", "Sheet3", "Sheet5" instead of consisting of three elements. Try this instead:

    Private Sub Worksheet_Activate()
    Dim Cell As Range
    Dim arrSheets() As String
    Dim i As Integer

    i = -1
    'Loop through the list of grouped sheets
    For Each Cell In Worksheets("Sheet6").Range("GroupedSheets")
    i = i + 1
    ReDim Preserve arrSheets(i)
    arrSheets(i) = Cell
    Next Cell

    Sheets(arrSheets).Select
    End Sub

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Grouping Sheets Dynamically (Excel 2000, SP2)

    Select, as the name indicates, selects one or more worksheets. It does not return a value, so you cannot use If Something.Select Then. You would have to check the ActiveWindow.SelectedSheets collection to find out which sheets are currently selected. But I feel very uneasy about using the SelectionChange event for automatically selecting and deselecting worksheets. I would avoid it if possible.

  4. #4
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping Sheets Dynamically (Excel 2000, SP2)

    Many thanks! I figured it had something to do with the array not getting separate elements, but I wasn't sure, and even if I had been, I didn't know how to fix it. But now I have another problem. In the Selection_Change event of the same sheet, I had similar code which tracked the selection and ungrouped the sheets if the user moved out of the selection that repeats on all sheets. It works fine with the sheet names hard-coded, but I get an error at the yellow highlight:

    <pre>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Cell As Range
    Dim arrSheets() As String
    Dim i As Integer

    i = -1

    'Loop through the list of grouped sheets
    For Each Cell In Worksheets("GroupedSheets").Range("GroupedSheets")
    i = i + 1
    ReDim Preserve arrSheets(i)
    arrSheets2(i) = Cell
    Next Cell

    'If the user is in the outline, select both sheets
    'Otherwise, select just the current sheet
    If Not Intersect(Range("Outline"), Target) Is Nothing Then
    'If the sheets aren't already grouped, group them
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">If Not Sheets(Array(arrSheets)).Select</span hi> Then
    Sheets(Array(arrSheets)).Select
    Exit Sub
    End If
    Else
    'If they are grouped, ungroup them
    If <span style="background-color: #FFFF00; color: #000000; font-weight: bold">Sheets(Array(arrSheets)).Select</span hi> Then
    Me.Select
    End If
    End If

    End Sub</pre>



    Any ideas why it isn't working here? The error message is "Method 'Select' of object 'Sheets' failed." Sample that recreates the error attached. Thanks! [NOTE: File will be attached later. I have to reboot. Getting VERY strange error messages. . .]

    [LATER: File is now attached, but something kept making Excel crash, so I'm not sure if you want to experiment with it!]

    BTW, I tried making arrSheets a public variable so I wouldn't have to run the code again after the sheet was selected, but it didn't like that, either.

    --Karyl

  5. #5
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping Sheets Dynamically (Excel 2000, SP2)

    I was using similar code in another sheet, and it appeared to be working, but I'm not sure why. I wan't getting an error message, however. But I was specifically naming the sheets rather than looping through them. I've fixed both sheets now to something like this:

    <pre>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Cell As Range
    Dim arrSheets() As String
    Dim i As Integer

    If Not Intersect(Range("Outline"), Target) Is Nothing Then
    i = -1
    'Loop through the list of grouped sheets
    For Each Cell In Worksheets("GroupedSheets").Range("GroupedSheets")
    i = i + 1
    ReDim Preserve arrSheets(i)
    arrSheets(i) = Cell
    Next Cell

    'Group the sheets
    Sheets(arrSheets).Select
    Else
    Me.Select
    End If

    End Sub</pre>



    It appears to be working as I intended. As long as the user is in the "Outline" section of the first sheet, all the sheets are grouped. If the user moves outside the outline, the sheets are ungrouped. If you don't recommend using the Selection Change event for this, how would you do it? I cannot depend on the users remembering to group the sheets when they make edits to the outline, which much carry forward on all the selected sheets.

    --Karyl

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Grouping Sheets Dynamically (Excel 2000, SP2)

    I hadn't gotten round yet to testing the code, and I was afraid that it might have undesired side effects. But if it works OK for you, that's fine, no need to change it.

Posting Permissions

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