I'm attempting to create a task tracker on Excel 2003 and have come across a couple of complications in attempting to get the tracker to function exactly how I'd prefer it to.
#1. Filter Automatically Refreshes on worksheet change to ensure current filter is applied on worksheet change.
I set up an autofilter to filter by dates or names in ascending or descending order, but I would like this to refresh automatically when a new entry is added to the table based on the currently set filter.
Eg. You add a new task at the bottom of the table. It is due in two days, so it shoots to the top of the list without needing to reselect 'Ascending'.
#2. Filter Ensures that 'CLOSED' items remain on the bottom even when new entries are added to the Task List
As I would like to have visibility of the closed/completed tasks, but I won't want them to appear before current tasks. I'd like them to be filtered and stay at the bottom of the table so they aren't being deleleted.
Please reference the included Excel document to see an example of what I am trying to accomplish.
To create an automatically refreshing filter I have attempted to use this code but on Excel 2003 it does not appear to work:
Private Sub Worksheet_Change(ByVal Target As Range)
If Me.FilterMode = True Then
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
With ActiveWorkbook
.CustomViews.Add ViewName:="Mine", RowColSettings:=True
Me.AutoFilterMode = False
.CustomViews("Mine").Show
.CustomViews("Mine").Delete
End With
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End If
End Sub
Thanks for your assistance
Bookmarks