Execute code when grouping buttons used

[jday]

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?

 

[IanKR]

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

 

[jday]

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

 

[IanKR]

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.