I was hoping to find a Worksheet command that could identify when a row
grouping was expanded or collapsed by user, then execute some additional code
immediately following the completion of that action. I thought it might work
with either Worksheet_Change or Worksheet_Calculation, but neither of these
seem to work. Any ideas?

I was hoping to find a Worksheet command that could identify when a row
grouping was expanded or collapsed by user, then execute some additional
code
immediately following the completion of that action. I thought it might
work
with either Worksheet_Change or Worksheet_Calculation, but neither of
these
seem to work. Any ideas?


Hi

I found a workaround for this. When you group/ungroup, the relevant rows are
hidden/unhidden. Unfortunately, hiding and unhiding rows doesn't in itself
fire up the Worksheet_Change or Worksheet_Calculation event. But you can get
around this by using the =SUBTOTAL function, with a first argument of 103,
which is COUNTA, ignoring hidden cells (Excel 2003 and later).

Say your grouped rows are Rows 3 to 5 inclusive, enter a 1 in a cell in each
of those rows in a column way over to the right of your data, say col X - so
put 1 in each cell in X3:X5. Then enter =SUBTOTAL(103,X3:X5) in say cell X1.
As the rows are hidden (grouped) / unhidden (ungrouped) cell X1 will return
0 or 3. This will trigger the Worksheet_Calculation event, that you can use
to fire up your code.

Hope this helps.

Ian

That works! Thank you so much for that creative approach!

"IanKR" wrote:

I was hoping to find a Worksheet command that could identify when a row
grouping was expanded or collapsed by user, then execute some additional
code
immediately following the completion of that action. I thought it might
work
with either Worksheet_Change or Worksheet_Calculation, but neither of
these
seem to work. Any ideas?


Hi

I found a workaround for this. When you group/ungroup, the relevant rows are
hidden/unhidden. Unfortunately, hiding and unhiding rows doesn't in itself
fire up the Worksheet_Change or Worksheet_Calculation event. But you can get
around this by using the =SUBTOTAL function, with a first argument of 103,
which is COUNTA, ignoring hidden cells (Excel 2003 and later).

Say your grouped rows are Rows 3 to 5 inclusive, enter a 1 in a cell in each
of those rows in a column way over to the right of your data, say col X - so
put 1 in each cell in X3:X5. Then enter =SUBTOTAL(103,X3:X5) in say cell X1.
As the rows are hidden (grouped) / unhidden (ungrouped) cell X1 will return
0 or 3. This will trigger the Worksheet_Calculation event, that you can use
to fire up your code.

Hope this helps.

Ian

That works! Thank you so much for that creative approach!

You're very welcome. I've always found the Worksheet_Change event a bit of a
disappointment, in that it doesn't always fire when you'd expect (you would
have thought that hiding/unhiding rows or columns would constitute a
"change", but apparently not, according to MS!). So I often have to think up
workarounds like this. They keep you on your toes, so to speak.