Jun 15 2022 01:36 AM
Hi all,
I am curently working on a general table for our company. I've made a table for our partners.
In the table they see some tasks, they all have responsibilities for different tasks, and they recieve points per task. But, they don't need to see the other partners. They now see a complete overview, I want to minimaze that.
So, I want an option to filter partners, and the result has to be that they only see their party and their responsibility. In the filter option, I also want the "All" function, so that they still can see a overall overview ( situation atm).
To show what I want, I've added the table that I use ( but blank), and the result I want.
Who can help me?
Thanks!
Result has to be:
Jun 15 2022 04:52 AM
SolutionI have attached a small demo workbook. You'll have to modify the code for your situation.
To view the code, right-click the sheet tab and select 'View Code'.
You'll need to allow macros when you open the workbook.
Jun 15 2022 05:44 AM
Jun 15 2022 06:01 AM
You can copy the code from the worksheet module in the demo workbook to the worksheet module in your own workbook.
You'll then have to modify it because your worksheet probably uses different columns for the partners.
You'll have to save it as a macro-enabled workbook (.xlsm).
If you wish, you can attach a copy of your workbook without sensitive data, or make it available through OneDrive, Google Drive, Dropbox or similar.
Jun 15 2022 06:18 AM
Still doesn't work.. I've copied the VBA and modified it, but it still didn't work.
Below my workbook without data. Ive 5 sheets in one workbook, so I've made a new workbook with only 1 sheet.
Jun 15 2022 07:17 AM
@BeyK40
Hopefully @Hans_Vogelaar doesn't mind me jumping in here and you should mark his response as 'Best Answer' as the hard yards are already done....
In your workbook, you have saved the code as modules, whereas a worksheet change routine needs to be in the MS Excel Objects for the specific worksheet. The next thing is that you need a data validation list for the filter items where the code is monitoring for the change. See attached the updated version:
Jun 16 2022 02:10 AM - edited Jun 16 2022 02:10 AM
Thanks!
One more question.
The cells with te partners refer to another sheet (main sheet). With data valadition there is no problem, still get a list to filter. But the code doesnt work.. Is there a way to fix this?
(the code does work if I just type in the partner, but I it has to refer to the main sheet, because if a partner changes, I wanted it to automatically change in all the sheets.)
Jun 16 2022 03:14 AM
Jun 16 2022 03:29 AM
An option would be to use the Worksheet_Calculate event, although the overhead is much larger.
See the attached version.
Jun 17 2022 12:21 AM
Jun 15 2022 04:52 AM
SolutionI have attached a small demo workbook. You'll have to modify the code for your situation.
To view the code, right-click the sheet tab and select 'View Code'.
You'll need to allow macros when you open the workbook.