Task List Question

[Michelle Hillard]

Hi guys, hope you can help.

I have one worksheet named 'Task'
I have attached a form with a calendar control, which when clicked on, I can
select a date, click on a button, and it inserts that date into the active
cell.
I wish to do that for the start date and due date columns.

It has 5 columns named:

A B C D E
Task List Start Date Due Date Complete Left (days) //Row 1

Task Entries start at row 2.

The Code I have is:

==================== This Workbook:

Private Sub Workbook_Open()
Sheets(1).Rows("2:" & ActiveSheet.UsedRange.Rows.Count).Sort _
Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = Sheets(1).Name And Target.Column = 5 Then Sheets(1).Rows _
("2:" & ActiveSheet.UsedRange.Rows.Count).Sort Key1:=Range("E2"),
Order1:=xlAscending, Header:=xlGuess
End Sub

===================== Userform (frmDateSelector)
Option Explicit
Option Compare Text
Option Base 1
Private Declare Function FindWindowA Lib "user32" (ByVal lpClassName As
String, ByVal lpWindowName As String) As Long
Private Declare Function EnableWindow Lib "user32" (ByVal hWnd As Long,
ByVal bEnable As Long) As Long
Dim mlHWnd As Long, mbModal As Boolean, mbDragDrop As Boolean

Private Sub cmdGoToday_Click()
CalDateSelector.Today
txtDate.Value = CalDateSelector.Value
End Sub

Private Sub spnMonth_SpinDown()
CalDateSelector.PreviousMonth
txtDate.Value = CalDateSelector.Value
End Sub

Private Sub spnMonth_Spinup()
CalDateSelector.NextMonth
txtDate.Value = CalDateSelector.Value
End Sub

Private Sub spnYear_SpinDown()
CalDateSelector.PreviousYear
txtDate.Value = CalDateSelector.Value
End Sub

Private Sub spnYear_Spinup()
CalDateSelector.NextYear
txtDate.Value = CalDateSelector.Value
End Sub

Private Sub UserForm_Initialize()
CalDateSelector.Value = Application.Range("Task!TodaysDate").Value
End Sub

Private Sub UserForm_Activate()
CalDateSelector.Value = Application.Range("Task!TodaysDate").Value
mlHWnd = FindWindowA("XLMAIN", Application.Caption)
EnableWindow mlHWnd, 1
mbDragDrop = Application.CellDragAndDrop
Application.CellDragAndDrop = False
End Sub

Private Sub CalDateSelector_Click()
txtDate.Value = CalDateSelector.Value
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Application.CellDragAndDrop = True
Unload frmDateSelector
End If
End Sub

Private Sub cmdPickDate_Click()
On Error Resume Next
txtDate.Value = CalDateSelector.Value
Application.ActiveCell.Value = CalDateSelector.Value
End Sub

Private Sub cmdClose_Click()
Unload frmDateSelector
Application.CellDragAndDrop = True
End Sub

================= Module 1
Sub ShowDatePicker()
frmDateSelector.Show
End Sub

=========================================== END OF CODE

What I can do so far is this:

If I manually enter a number in column E (Days Left) for example:

A B C D E
Task List Start Date Due Date Complete Left (days) //Row 1
Backup 5
Alerts 1

Then it changes to:

A B C D E
Task List Start Date Due Date Complete Left (days) //Row 1
Alerts 1
Backup 5

Which is what I want (ie.to have the least number of days tasks at the top
of the worksheet).

However,

What I would really like to do is not have to manually enter in these
numbers left and have it compute automatically days left by finding the
difference in days between column B (start date) and C (due date).
So I put in a formula in column E (=C2-B2).
When I did this, the automatic sorting of rows based on number of days left
stopped working.
It seems like the Workbook_SheetChange event stops working.
Is there a solution to this problem?

What I would also like to do is:
When I manually enter a 'y' in the Complete column D, to move this row to a
second worksheet, and append it to the next available row, underneath
existing rows.

Any help would be greatly appreciated.

Thanks in advance.

--


---------------------------------------------------------------------
"Are you still wasting your time with spam?...
There is a solution!"

Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.com