Oh, great and powerful Excel gods, please take mercy on my ignorant self!



I rarely use Excel, but for some reason continue to be selected to create worksheets!

Creating a basic 'Current Workload' (attached) tracker sheet with task specific metrics on length of time (in days/weeks) each takes to complete.

Specifically:

Column H, 'Status' whenever the (dropdown) value is changed, needs to:
1. Populate the change date in Column I, 'Status Date' - regardless of the selection.
Got this one working with the first VBA Sub
2. Populate the date in Column J, 'Assigned Date' that 'Assigned (not started)' option
is selected. Date must not change once entered.
3. Populate the date in Column K, 'Start Date' that 'In Progress' option is selected.
Date must not change once entered.
4. Populate the date in Column L, 'Date Completed' that 'Completed' or 'CANCELLED'
option is selected. Date must not change once entered.

And finally, Column L, 'Date Completed' needs to reconcile with Column G, 'Estimated Completion' and populate Column M, 'Task Time' and show total days/weeks it took to complete the task.

This is the VBA I used for Need #1:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("H2:H" & Rows.Count), Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Intersect(Range("H2:H" & Rows.Count), Target).Offset(0, 1).Value = Date
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

And it works...

I then tried adding this VBA to handle Need #2,3, and 4:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim WorkRng As Range
Dim Rng As Range
Dim DateCol As String

Set WorkRng = Intersect(Columns("H"), Target)
If Not WorkRng Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Select Case UCase(Rng.Value)
Case "Assigned Date"
DateCol = "J"
Case "In Progress"
DateCol = "K"
Case "Date Completed"
DateCol = "L"
End Select
If Len(DateCol) > 0 Then
With Cells(Rng.Row, DateCol)
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
End If
Else
Intersect(Rng.EntireRow, Union(Columns("J"), Columns("K:L"))).ClearContents
End If
Next
Application.EnableEvents = True
End If
End Sub

And it does NOT work. I have tried (unsuccessfully) to adjust these methods to work together, but I am simply guessing...

Everything is as I explained in the attached file, all my rules and VBA. Again, I don't know very much about any of this beyond the basics. So, I have no idea if what I am trying to do is even possible...

Please help...

Attached Files

TechPubs_Workload_Tracker_vA1.xlt (24.5 KB, 1 views)

Please add code tags around your code.
Next time it would be better to post in the VBA subforum. Thx

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post