I’m working on macro that will expand / collapse groups. The idea is to have label buttons ‘show detail’ and ‘hide detail’ that sit above the group
You can see an example here
http://www.youtube.com/watch?v=70efsNWwuIU#t=14m00s
here’s the macro so far

Sub group_expand()
''Macro to show the details of a group

‘Find the row that the button that called the procedure is in

buttonRow = ActiveSheet.Shapes(Application.Caller).TopLeftCell .Row

'Check to see if grouping details are already showing
If Worksheets("IS").Rows(buttonRow + 1).ShowDetail = True Then
‘If grouping detail is already showing, then do nothing
Else
‘If the grouping detail is hidden, then show the detail
Worksheets("IS").Rows(buttonRow + 1).ShowDetail = True
End If
End Sub

There are a few improvements I would like to make.

1.Find the top row of the next grouping. Currently the macro is hardcoded and label has to be exactly 1 row above the top of the grouping. I can find the row location of the button, however I don’t know how to find the top row of the next group.

In other words, I’d replace the (buttonRow + 1) with (buttonRow + distance to top row of next grouping),

e.g.

If Worksheets("IS").Rows(buttonRow + 1).ShowDetail = True

To

If Worksheets("IS").Rows(buttonRow + [distance to top row of next grouping]).ShowDetail = True

2. Find the height of the grouping

Here’s where I’m up to.

ActiveSheet.Activate
Set myRange = ActiveCell.CurrentRegion
lastRow = myRange.Rows.Count

This will return the correct height of the grouping. However, I need this driven off a cell reference, not ActiveCell.


My first thought was to make the following change:

Set myRange = Cells(BR + [distance to top row of next grouping],1).CurrentRegion
lastRow = myRange.Rows.Count

But this doesn’t work. The value of lastRow is always 1 in this example.

If anyone has a better way to determine the height of a grouping I would love to hear.

3. Finally, check to see if the summary rows are at the top of the grouping or at the bottom of the grouping (i.e. does the +/- show up at the top of the grouping or at the bottom of the grouping).

As this is currently coded, the macro only works if the summary rows are set to be at the top of the grouping. If the summary row (+/- button) is at the bottom of the grouping then I need to add not 1, but the 1 + height of the grouping to the buttonRow.

I’m sure the location of the summary row/(+/-) is a property of groupings, I just don’t know what the property is.

I do similar in my Invoicing: Simple Bookkeeping app on the Income /
Expense Summary sheet, where users can double-click a group (cell in
any visible row) to expand/collapse (toggle) rows for that group. I
also add right-click menu items to do this for all groups. This works
via an events handler class in the project. The worksheet is protected
because its content is 'read only' and so is fully formula driven.

In this sheet, the Income/Expense categories show subtotals for the
subaccounts under them. Expanding the groups lets users see a detail
view of a parent account.

I'm thinking this might be fairly complex to describe how to achieve
this here, so maybe someone has a simpler solution to offer.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

yes, I'm sure that there's a simple answer. No reason for this to be complex.

All you need is:

-find the start of the next group relative to an active cell
-find where setting for whether the summary row is at the top / bottom of the group

On Friday, April 27, 2012 3:49:27 PM UTC-4, GS wrote:
I do similar in my Invoicing: Simple Bookkeeping app on the Income /
Expense Summary sheet, where users can double-click a group (cell in
any visible row) to expand/collapse (toggle) rows for that group. I
also add right-click menu items to do this for all groups. This works
via an events handler class in the project. The worksheet is protected
because its content is 'read only' and so is fully formula driven.

In this sheet, the Income/Expense categories show subtotals for the
subaccounts under them. Expanding the groups lets users see a detail
view of a parent account.

I'm thinking this might be fairly complex to describe how to achieve
this here, so maybe someone has a simpler solution to offer.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Victor presented the following explanation :
yes, I'm sure that there's a simple answer. No reason for this to be complex.

All you need is:

-find the start of the next group relative to an active cell

I use helper cells in a hidden column for this. Very simple coding that
makes use of local-scope defined names. It's the events handler class
that poses difficulties for most people, but you could use dedicated
menuitems on the Tools menu (or your own menu) if you don't need your
own right-click popup menu.

-find where setting for whether the summary row is at the top / bottom of the
group

Not necessary when the setting is already set since it can't be done on
a 'per group' basis (AFAIK, this is a sheetwise setting). This is a
simple matter of incrementing/decrementing the OutlineLevel for the
selected group[s]. My project just has one level to deal with, but I
coded for using more.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion