Excel - macro to show / hide grouping, linked to label button

I=92m working on macro that will expand / collapse groups. The idea is to h=
ave label buttons =91show detail=92 and =91hide detail=92 that sit above th=
e group =20
You can see an example here
http://www.youtube.com/watch?v=3D70efsNWwuIU#t=3D14m00s
here=92s the macro so far

Sub group_expand()
''Macro to show the details of a group
=20
=91Find the row that the button that called the procedure is in
=20
buttonRow =3D ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
=20
'Check to see if grouping details are already showing
If Worksheets("IS").Rows(buttonRow + 1).ShowDetail =3D True Then
=91If grouping detail is already showing, then do nothing
Else
=91If the grouping detail is hidden, then show the detail
Worksheets("IS").Rows(buttonRow + 1).ShowDetail =3D 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 a=
nd 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=92t know how to find the top =
row of the next group.=20

In other words, I=92d replace the (buttonRow + 1) with (buttonRow + distanc=
e to top row of next grouping),

e.g.

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

To=20

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

2. Find the height of the grouping

Here=92s where I=92m up to.=20

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

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


My first thought was to make the following change:
=20
Set myRange =3D Cells(BR + [distance to top row of next grouping],1).Curren=
tRegion
lastRow =3D myRange.Rows.Count

But this doesn=92t work. The value of lastRow is always 1 in this example.=
=20

If anyone has a better way to determine the height of a grouping I would lo=
ve to hear.=20

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 se=
t to be at the top of the grouping. If the summary row (+/- button) is at t=
he bottom of the grouping then I need to add not 1, but the 1 + height of t=
he grouping to the buttonRow.=20

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

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 am 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 am sure that there is 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

Victor presented the following explanation :

I use helper cells in a hidden column for this. Very simple coding that
makes use of local-scope defined names. it is 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 do not need your
own right-click popup menu.

Not necessary when the setting is already set since it cannot 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