Hello everyone,
I'm a new comer, very nice to be here.
I'm totally new to VBA codes and macros (just watched some videos tutorials and online discussions today), and couldn't think of any solutions for what I want to do, so hope some of you could help.
I'm using MS Office Excel 2013, and my operating system is Windows 7. I created a gantt chart (Bar chart) with a list of tasks, containing task name, start date, deadline, duration, and remaining days. Duration and remaining days of the tasks are calculated automatically using functions. I don't like the cells with words "completed/incompleted", so I used conditional formatting to mark a task completed (double click the cell besides a task (done column) to show a smiling face and the task row is filled with green background with the fonts strikethrough; if the current day is over the deadline but the cell besides the task (done column) is still empty, then show a sad face, and the task row is filled with red background). I can't show the background colors in the table below but you get what I mean.
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Done
[/TD]
[TD]Tasks
[/TD]
[TD]Start date
[/TD]
[TD]Deadline
[/TD]
[TD]Duration
[/TD]
[TD]Remaining
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Task1[/TD]
[TD]21.08.2016[/TD]
[TD]31.08.2016[/TD]
[TD]11[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Task2[/TD]
[TD]11.08.2016[/TD]
[TD]20.08.2016[/TD]
[TD]10[/TD]
[TD]-3[/TD]
[/TR]
</tbody>[/TABLE]
Now I want to use a Combo Box to filter the tasks (I very dislike the Excel build-in filter function, I would like a separate dropdown filter from the tasks table), but with some fixed options which are NOT found from the dataset. I want the Combo Box to have 4 options: All, Completed, Incompleted, Overdue tasks. Surely it's possible to use conditional formatting to show in another column whether the task is completed or not, but then the options in the dropdown menu would be based on the completion column, if there is no task completed, there won't be such an option in the dropdown filter as "completed"; the same goes for "All" and "Overdue". I would need the result that even when there's no task completed or overdue, there are still the option "completed", "overdue" (and of course also "all"), and when clicking on these option, there's nothing to show except for the titles (tasks, start date,...), all the tasks are filtered out (hidden also with the bar chart). Are there such codes which could solve this?
I have searched for possible solutions which apply dependent combo box selections to a filter, where the original data is within 1 worksheet and the filtered data is in another worksheet (of course the combo box selections are still dependent on the cells values and thus not useful for me). But I would like not to use multiple worksheets and rather hope to realize it in 1 worksheet. Is it possible? I haven't found any feasible solutions for my problem (not even similar problems) and I don't have any idea of using the codes, so I haven't tried any codes.
Hope it is clear for you to understand the problem. Thanks a lot in advance and apologize for the long text.
I'm a new comer, very nice to be here.
I'm totally new to VBA codes and macros (just watched some videos tutorials and online discussions today), and couldn't think of any solutions for what I want to do, so hope some of you could help.I'm using MS Office Excel 2013, and my operating system is Windows 7. I created a gantt chart (Bar chart) with a list of tasks, containing task name, start date, deadline, duration, and remaining days. Duration and remaining days of the tasks are calculated automatically using functions. I don't like the cells with words "completed/incompleted", so I used conditional formatting to mark a task completed (double click the cell besides a task (done column) to show a smiling face and the task row is filled with green background with the fonts strikethrough; if the current day is over the deadline but the cell besides the task (done column) is still empty, then show a sad face, and the task row is filled with red background). I can't show the background colors in the table below but you get what I mean.
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Done
[/TD]
[TD]Tasks
[/TD]
[TD]Start date
[/TD]
[TD]Deadline
[/TD]
[TD]Duration
[/TD]
[TD]Remaining
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Task1[/TD]
[TD]21.08.2016[/TD]
[TD]31.08.2016[/TD]
[TD]11[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]
[/TD][TD]Task2[/TD]
[TD]11.08.2016[/TD]
[TD]20.08.2016[/TD]
[TD]10[/TD]
[TD]-3[/TD]
[/TR]
</tbody>[/TABLE]
Now I want to use a Combo Box to filter the tasks (I very dislike the Excel build-in filter function, I would like a separate dropdown filter from the tasks table), but with some fixed options which are NOT found from the dataset. I want the Combo Box to have 4 options: All, Completed, Incompleted, Overdue tasks. Surely it's possible to use conditional formatting to show in another column whether the task is completed or not, but then the options in the dropdown menu would be based on the completion column, if there is no task completed, there won't be such an option in the dropdown filter as "completed"; the same goes for "All" and "Overdue". I would need the result that even when there's no task completed or overdue, there are still the option "completed", "overdue" (and of course also "all"), and when clicking on these option, there's nothing to show except for the titles (tasks, start date,...), all the tasks are filtered out (hidden also with the bar chart). Are there such codes which could solve this?
I have searched for possible solutions which apply dependent combo box selections to a filter, where the original data is within 1 worksheet and the filtered data is in another worksheet (of course the combo box selections are still dependent on the cells values and thus not useful for me). But I would like not to use multiple worksheets and rather hope to realize it in 1 worksheet. Is it possible? I haven't found any feasible solutions for my problem (not even similar problems) and I don't have any idea of using the codes, so I haven't tried any codes.
Hope it is clear for you to understand the problem. Thanks a lot in advance and apologize for the long text.
