Execute code when grouping buttons used
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? |
Execute code when grouping buttons used
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 |
Execute code when grouping buttons used
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 |
Execute code when grouping buttons used
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. |
All times are GMT +1. The time now is 09:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com