Forum Discussion
Macro Buttons
I have been asked to complete some "buttons" in a working excel spreadsheet that allow the users to:
- Sorts the sheet in relation to a cell colour (certain rows are assigned to categories by a cell colour)
- Hides the rows of a certain colour (once completed they are shaded grey by another macro - but will still need to be viewed - so want the option to hide)
- Allows these actions to be reversed (unhide rows, revert to original)
I also want it to be able to self update when more rows are added/deleted, when different colours are assigned etc.
I am so over my head. I tried recording macros and used the custom sort feature - but I need to go in and manually change row numbers..... and the "hide rows" for grey doesn't work as more rows become grey.....
2 Replies
First of all, get rid of the merged cells in your header row (use Center Across Selection in stead) and put the headers directly above the data range (i.e. no blank row in between). Then, put filter buttons on the header.
Next, get rid of all the individual conditional formatting rules that colour most of the cells grey. You can replace it by one single rule like:
=$I12="x" and apply it to the entire data range.
Now you are all set to use the "filter by color" feature. In the attached file I have set the filter in the Description column to view only rows with "No fill", thus hiding the grey ones. But you could also filter out all the rows with "x" in the Closed column. The "Action by" column was filtered to show only orange cells. These are just examples of what you can do with a few clicks. No need for complicated VBA code. Your users could learn to do that, I believe. But, if they insist on buttons, you could carefully record every thinkable (un)filter action and add one to just "clear all filters" (i.e. revert).
Hi
I'm afraid what you are asking will require a lot of investment from a VBA developer to help you - and I suspect many will not have the time to assist you - including me, sorry!
Having said that you have obviously made some good progress to get to this stage with the buttons and some code.
Good luck!
Peter
