Execute Excel Code When a Group of Buttons are Used
Sponsor
Execute Excel Code When a Group of Buttons are Used [Q]
Im confuse when trying to execute excel code in multi buttons i used, i was hoping to find an excel Worksheet command that could identify when a row grouping was expanded or collapsed by user, then execute some additional excel code immediately following the completion of that action. I thought it might work with either
Worksheet_Change
or
Worksheet_Calculation,
but neither of these excel code seem to work. Any ideas?
Execute Excel Code When a Group of Buttons are Used [A]
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.
Rate This Tips:
(1 votes, average: 5.00 out of 5)
Loading ...Incoming excel search terms
excel rows group,excel ungroup code,buttons used in excel,check rows group/ungroup or not,copying grouped rows in excel,excel code tags,excel codes group,excel counta col,excel counta for buttons macro,excel delete a collapsed group,excel expand ungroup sign cells,excel functions used to group the data,excel grouping,excel grouping code dynamic hiding,excel subtotal 103 not working,excel tips group column,excel transfer rows based on criteria to another worksheet,excel using vba to ungroup data,excel vba check group rows is expanded or not,excel vba columns ungroup,excel vba create button group,excel vba for each button in a group,excel vba group columns,excel vba group ungroup,excel vba grouping and buttons,excel vba introduce execute,execute all formulas in excel,execute vba from worksheet?,executeexcel print,executing formula in excel,group columns buttons excel,group rows with buttons,group/ungroup columns and rows in ms excel,grouping/ungroup in excel using vba,hiding and unhiding rows in visual,how to execute date minus functions in excel,minus function in excel for a group,subtotal vba excel,ungroup columns excel spreadsheet,ungroup excel vba,ungroup excel visual basic,use vba check rows is group/ungroup,vba check rows group ungroup,vba excel execut a code from another sheet,vba excel expand group on protected worksheet,vba exel subtotal,vba group/ungroup column excel
Related Excel Tips
Comments
Have another excel answer or questions for this problem ?
Feel free to post it here..
Best Excel Tips
- Calling Macro Visual Basic Procedures of a Different Project (5.00 out of 5)
- How to Remove Excel Vba Macro Button (5.00 out of 5)
- Delete Worksheet (5.00 out of 5)
- Copy from Worksheet (5.00 out of 5)
- How to Calculate 6 Working Days Between Two Dates (5.00 out of 5)
- Remove Hyperlinks in Excel (5.00 out of 5)
- Unprotect Worksheet Password (5.00 out of 5)
- Calling Macro Visual Basic Procedures of a Different Project
