Expand rows automatically based on grouped rows(Expansion and Contraction)

Respected all of u,

The workbook is extracted from http://www.vbaexpress.com/forum/show...e-unhide-sheet given by VBAX Guru mikerickson .

I have group the rows D3 : D4 which represents to B2 heading.Similarly,B6 :B9 represents to B5 heading and so on in Master Sheet.

Now when you move from B2 to A2 then A2 gets checked on and again when you go on A2 then it will get unchecked.Similarly, when moved from B5 to A5 the same will be checked on and off.

Here, what I want is that it should get automatically expand below rows which has been grouped when once get checked on.Suppose, when B2 heading is get checked on A2 the rows below should get expand automatically.

Similarly,when B5 heading is get checked on A5 the rows below should get expand automatically and so on.

Others things remaining the same.

NB:The same is posted by me at :http://www.vbaexpress.com/forum/show...n-grouped-rows

But no reply received till now.

Can be achieved by adding the red line :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        If .Cells.Count = 1 Then
            If SheetExists(.Offset(0, 1).Value) Then
                .Value = IIf(.Value = vbNullString, "a", vbNullString)
                Target.Offset(1).EntireRow.ShowDetail = IIf(.Value = vbNullString, False, True)
                .Font.Name = "Marlett"

Thanx for your kind reply.

It worked on A column.This I want in A column only for expanding and contracting.

But it is prompting an error in C & E Column( might be there further like this column in future) as they are simply the sub category of A column, Sheet to be displayed/hidden based on check /uncheck which do not require expansion or contraction as it is already done by A column check/uncheck which you have mentioned in above post.

i.e sub category (C,E,...) should not prompt an error on checked/unchecked as they do not require expansion/contraction simply hide/display sheet based on check/uncheck.

I think I may be able to make it understand.

I don't quite catch what you mean, but :

- If you want the expand/collapse only occurred on column A, then change the red line to this :

If Target.Column = 1 And Not IsEmpty(Target.Offset(1, 3)) Then Target.Offset(1).EntireRow.ShowDetail = IIf(.Value = vbNullString, False, True)

- If you want the expand/collapse occurred on column C and E as well, then :
a. You must create another sub group(s) as seen on sheet "Master 2" on my attached file
b. Change the red line to this :

If Not IsEmpty(Target.Offset(1, 3)) Then Target.Offset(1).EntireRow.ShowDetail = IIf(.Value = vbNullString, False, True)

Thanx perhaps in your previous post something was missing in code and now it worked perfectly as expected.However 2nd part of code which you have mentioned momentarily not required but will required definitely in future.

Two more things I would like to request from you is that, I have changed some sheet name in D and E column which is subgroup of B2 and B5 (momentarily might be further while increasing.

Now, here what I want is that when in C and E is checked the name displayed in worksheet is different than those mentioned in D & E column which is subgroup of B2 and B5.I want to make similar to that as displayed in D and E column.

Secondly & Lastly, C and E have been coded as dependent i.e without checking D cannot be displayed. i.e for example when C7 is unchecked then E8 and E9 won't display the sheet even though it is checked on.I want C and E and further towards right hand side column to be independent.

Hoping again a positive response.

Originally Posted by paradise2sr

I have changed some sheet name in D and E column which is subgroup of B2 and B5 (momentarily might be further while increasing.
Now, here what I want is that when in C and E is checked the name displayed in worksheet is different than those mentioned in D & E column which is subgroup of B2 and B5.I want to make similar to that as displayed in D and E column.

I don't understand what you mean.

Originally Posted by paradise2sr

C and E have been coded as dependent i.e without checking D cannot be displayed. i.e for example when C7 is unchecked then E8 and E9 won't display the sheet even though it is checked on.I want C and E and further towards right hand side column to be independent.

Please check the attached file.

You have correctly solved my post #5 namely "Secondly & Lastly"
Heartily Thanx for that.

With Regard to my query in Post #5 first part which you did not understand is that in "Master" worksheet in D & F column the worksheet name I have mentioned has been changed when you see Post #5 attachment.When corresponding is checked in "Master" sheet it will display the worksheet.I want the uniformity with the name mentioned in "Master" sheet and displayed sheet based on checked on.

Since duplicate sheet name is not allowed,so I have made the font white from right side in Master sheet the name and looks same and even though there is difference in sheet name as duplicate are not allowed.You can see the difference when you make the font all black.In similar pattern would this be done when sheet displayed on checked on so that it can match the sheet name with that of name mentioned in "Master" .

If you require further information,then kindly let me know.

Pls ignore this post 8 as the queries raised in this post 8 has been resolved by me.

Kindly go through my last queries as requested in Post 7

Look at this file : --removed--

There is a new sheet called "Aliases" which will store information about the "alias name" that is used as cell's content on sheet Master, paired with its actual sheet's name.
First column (Column A) of this sheet, is the "alias name". The rest of columns (B, C, ... if any) are the actual sheet's name associated with the "alias name"
For example for Aliases!A54, SM-Q1 alias name is used for actual sheet's name SM-Q1 (Aliases!B54) and L_SM-Q1 (Aliases!C54).
So if click on Master!E3, both these sheets (SM-Q1 and L_SM-Q1) will toggled their visibility.
You must manually update this "Aliases" sheet everytime you add/delete/change a sheet.

First of all let me thank you that you have helped me a lot.

What I mean to say that Aliases should be my tab name and Actual Sheet name is the name that is present in Master sheet.
Since,as per rule-If two tab name (Alias) is same then excel prompt an a message "That name is already taken.Try a different one." which means duplicate tab/sheet name is not allowed and to create something must be different .

For e.g

Tab name (Alias) Actual Sheet name (in Master sheet)

SM-Q1 SM-Q1
SM-Q1 SM-Q1 (ABC)
SM-Q1 SM-Q1(PM)

Here, tab name 'SM-Q1' which is repeated thrice.Excel will not allow to create more than one time.I want to know is it possible to create by VBA same tab name thrice or 4 times or ..... times which is simply the sub category of B and D column i.e D is the subset of B and F,H,K,... columns are the subset of D .

Hope you have understood well.The link to the post 9 is my original file.

If it is not possible,then kindly let me know,so that I can mark this file "As solved".

You are welcome.

Originally Posted by paradise2sr

I want to know is it possible to create by VBA same tab name thrice or 4 times

No.

You are welcome, thanks for the rep.points.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim rng As Range, cell As Range, arr, i As Long, j As Long, v
  With Target
    If .Count > 1 Or .Column Mod 2 = 0 Or IsEmpty(.Offset(, 1)) Then Exit Sub

    Application.ScreenUpdating = False
    
    .Font.Name = "Marlett"
    .Value = IIf(.Value = vbNullString, "a", vbNullString)
    Application.EnableEvents = False
      .Offset(0, 1).Select
    Application.EnableEvents = True
    On Error Resume Next
       Sheets(.Offset(0, 1).Value).Visible = IIf(.Value = vbNullString, xlSheetHidden, xlSheetVisible)
    On Error GoTo 0

    If .Column = 1 And .Offset(1).EntireRow.OutlineLevel > 1 Then
       On Error Resume Next
          .Offset(1).EntireRow.ShowDetail = IIf(.Value = vbNullString, False, True)
          Set rng = Intersect(Range(Target, Target.End(xlDown).Offset(-1)).EntireRow, ActiveSheet.UsedRange.Offset(, 2)).SpecialCells(xlCellTypeConstants)
          If Not rng Is Nothing Then
             For Each cell In rng
                 If cell.Column Mod 2 = 0 Then
                    If .Value = vbNullString Then
                       Sheets(cell.Value).Visible = xlSheetHidden
                    Else
                       Sheets(cell.Value).Visible = IIf(cell.Offset(0, -1).Value = vbNullString, xlSheetHidden, xlSheetVisible)
                    End If
                 End If
             Next cell
          End If
       On Error GoTo 0
    End If

    Application.ScreenUpdating = True
  End With
End Sub