Outline Showlevel - Current max level as variable?

[Mike1987]

Hi all,

Firstly, this is a cross post from Mr Excel.

I'm trying to create a shortcut via a VBA macro to increase and decrease the group level that is currently on show. I have found and written some code to find the maximum level on the active sheet and can therefore expand all levels or collapse all levels at once. To open or close one level at a time I would need to have access to the current maximum level open on the sheet, not the max level in general ... is this possible? Then I could simply set the current max opened level to variable and set the program to increment or decrease this number by one each time.

I did originally set the level back to 1 as a default and track a variable but this caused problems when switching between different sheets, resulting in many uses of the shortcut sometimes before it actually decreased anything ...

Any ideas?

Many thanks.

Current code.

Please Login or Register  to view this content.

A kind of example of what I would like to do ...

Please Login or Register  to view this content.

[Mike1987]

Bump ... It's looking more likely that this isn't possible to do ... ?

[CAntosh]

It's difficult to determine what you're hoping to do without seeing a sample workbook alongside your explanation in the initial post. Can you post a sample workbook with any sensitive data altered/removed?

[Mike1987]

I have attached a sample but it's just three sets of grouped data ... The code above returns how many "Group Levels" are on the sheet. In the attachment this would be 4. I would like to find out if there is a way of returning the current max open group. In the attached it would be 2 as I have saved it.

Let me know if you need any further explanation, but I'm not sure how how else to describe it.

[CAntosh]

I've never really used groups in my line of work, so bear with me here, I'm learning... but I think the procedure below will work? It should expand one group level (=more detail) from whatever is currently showing. I think. Let me know if this is the sort of thing you're looking for.

Please Login or Register  to view this content.

*EDIT: You can move in the other direction (contract) by changing the last row to ColLevel - 1

[Mike1987]

So simple ... Using the hidden column to skip the check will find only the max opened group ... Very good, works like a charm! Thanks very much for your help :D