+ Reply to Thread
Results 1 to 14 of 14

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

  1. #1
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2021 / 365
    Posts
    1,238

    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.
    Attached Files Attached Files
    Last edited by paradise2sr; 10-20-2016 at 12:22 PM.

  2. #2
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

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

    Can be achieved by adding the red line :
    Please Login or Register  to view this content.
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  3. #3
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2021 / 365
    Posts
    1,238

    Smile Re: Expand rows automatically based on grouped rows(Expansion and Contraction)

    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.
    Last edited by paradise2sr; 10-20-2016 at 10:09 PM.

  4. #4
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

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

    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 :
    Please Login or Register  to view this content.
    - 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 :
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2021 / 365
    Posts
    1,238

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

    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.
    Attached Files Attached Files
    Last edited by paradise2sr; 10-21-2016 at 02:29 AM.

  6. #6
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

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

    Quote Originally Posted by paradise2sr View Post
    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.



    Quote Originally Posted by paradise2sr View Post
    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.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2021 / 365
    Posts
    1,238

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

    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.
    Last edited by paradise2sr; 10-21-2016 at 09:48 AM.

  8. #8
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2021 / 365
    Posts
    1,238

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

    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
    Last edited by paradise2sr; 10-22-2016 at 12:42 AM.

  9. #9
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

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

    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.
    Last edited by karedog; 10-22-2016 at 12:43 PM.

  10. #10
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2021 / 365
    Posts
    1,238

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

    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".
    Last edited by paradise2sr; 10-22-2016 at 12:12 PM.

  11. #11
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

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

    You are welcome.

    Quote Originally Posted by paradise2sr View Post
    I want to know is it possible to create by VBA same tab name thrice or 4 times
    No.

  12. #12
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2021 / 365
    Posts
    1,238

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

    Can you kindly revised my original file that you have uploaded in post 9 removing "Alias" worksheet and code regarding Alias and retaining only the formula for contraction and expansion and independent displaying sheet.

    And Thanx once again for your kind effort and consistent help to resolve the issue.

    I am marking this thread as solved.

  13. #13
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

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

    You are welcome, thanks for the rep.points.

    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2021 / 365
    Posts
    1,238

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

    Awesome for the short and sweet code

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Is there a way to expand grouped column and rows when....
    By dbravo in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-24-2014, 04:42 PM
  2. Excel Macro to Collapse/Expand grouped rows and columns
    By magjayeck in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-28-2013, 10:58 AM
  3. Insert Row into Grouped Rows - Doesn't expand grouping
    By andrew c. in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-28-2011, 04:22 PM
  4. Group Contraction/Expansion in Protected Worksheet
    By rj in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-19-2005, 04:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1