Date for Task Tracker

[alexwoje]

Hello,

I am trying to create a task tracker for my team and am trying to create a macro that will add the date/name in when a task is selected from a dropdown in that same row, like below:

(add date)

(add name)

Task Selected

I want it to go Task Selected>name/date added. I also want to make sure that the date doesn't change once added to the sheet, is that possible? I am just starting out with VBA and have found similar examples but can't seem to tweak them properly to get what I am looking for. Thank you.

 

[Joe4]

Welcome to the Board!

Can you provide more information?
How exactly are these Tasks being selected/added?
What range is the table you show in your original post found in?
Do you have any current VBA code in your project (if so, please post it)?

 

[alexwoje]

Thank you for your reply, I found a template that works sort of like I want , this is the code:

Sub Intialize()

Dim iRow As Long

iRow = Sheets("TimeTracker").Range("H" & Application.Rows.Count).End(xlUp).Row + 1

'Code to Validate

If Sheets("TimeTracker").Range("F" & iRow).Value = "" Then

Sheets("TimeTracker").Range("B" & iRow).Value = Format([Today()], "DD-MMM-YYYY")
Sheets("TimeTracker").Range("C" & iRow).Value = Application.UserName

End If

End Sub

 

[Joe4]

Note regarding this line:

Sheets("TimeTracker").Range("B" & iRow).Value = Format([Today()], "DD-MMM-YYYY")

This will actually write the date as string (text), and not as an actual date.
So if you wanted to do any sorting or any kind of calculation on this field, it probably would not work the way you want.
It is usually best to write it as a date, any just apply whatever date formatting you want to the cell/column, i.e.

Sheets("TimeTracker").Range("B" & iRow).Value = Date()
Sheets("TimeTracker").Range("B" & iRow).NumberFormat = "dd-mmm-yyyy"

 

[alexwoje]

Whoa, thank you for pointing that out, I'll update the macro. C an I ad you another question? The full code for all the macros in the workbook is below:

Sub Intialize()

Dim iStartTimeRow As Long
Dim iEndTimeRow As Long
Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("TimeTracker")

iStartTimeRow = sh.Range("G" & Rows.Count).End(xlUp).Row 'Identify the last row for Start Time

iEndTimeRow = sh.Range("H" & Rows.Count).End(xlUp).Row 'identify the last row for End Time

'if Start Time and End Time row are not same it means one activity is pending.

If iStartTimeRow <> iEndTimeRow Then

MsgBox "There is an open task that yet to be completed.", vbOKOnly + vbInformation, "Open Task"

sh.Rows(iStartTimeRow).Select 'Selecting the row where end time is not updated

Else

'if Start and End Time row are same then need to update date in next row

sh.Range("A" & iStartTimeRow + 1).Value = Format([Today()], "DD-MMM-YYYY")

End If

'------------------------------------------------------------------------------------------------------------------------

'If Name is blank then update the user name in cell B7

If sh.Range("B7").Value = "" Then

sh.Range("B7").Value = Environ("username")

End If


End Sub



Sub Name_Date()

Dim iRow As Long

iRow = Sheets("TimeTracker").Range("C" & Application.Rows.Count).End(xlUp).Row + 1

'Code to Validate

If Sheets("TimeTracker").Range("I" & iRow).Value = "" Then

Sheets("TimeTracker").Range("A" & iRow).Value = Format([Today()], "DD-MMM-YYYY")
Sheets("TimeTracker").Range("B" & iRow).Value = Application.UserName

End If

End Sub


Sub Start_Time()

Dim iRow As Long

iRow = Sheets("TimeTracker").Range("I" & Application.Rows.Count).End(xlUp).Row + 1

'Code to Validate

If Sheets("TimeTracker").Range("D" & iRow).Value = "" Then

MsgBox "Please select the Task from the drop down.", vbOKOnly + vbInformation, "Task Blank"
Sheets("TimeTracker").Range("D" & iRow).Select
Exit Sub

ElseIf Sheets("TimeTracker").Range("G" & iRow).Value <> "" Then

MsgBox "Start Time is aleady captured for the selected Task."
Exit Sub
Else

Sheets("TimeTracker").Range("G" & iRow).Value = [Now()]

Sheets("TimeTracker").Range("G" & iRow).NumberFormat = "hh:mm:ss AM/PM"

End If

End Sub


Sub End_Time()

Dim iRow As Long

iRow = Sheets("TimeTracker").Range("I" & Application.Rows.Count).End(xlUp).Row + 1


'Code to Validate

If Sheets("TimeTracker").Range("G" & iRow).Value = "" Then

MsgBox "Start Time has not been captured for this task."
Exit Sub
Else

Sheets("TimeTracker").Range("H" & iRow).Value = [Now()]

Sheets("TimeTracker").Range("H" & iRow).NumberFormat = "hh:mm:ss AM/PM"

Sheets("TimeTracker").Range("I" & iRow).Value = Sheets("TimeTracker").Range("H" & iRow).Value - Sheets("TimeTracker").Range("G" & iRow).Value

Sheets("TimeTracker").Range("I" & iRow).NumberFormat = "hh:mm:ss"

End If

'Fill the Date and Name in next row
Call Intialize

End Sub



The problem I am running into/the question that I have is that once a user clicks the end time button, their name and date is automatically populated in the next row down. Is there a way that I can make it so their name/date populates once the task is chosen? I've upload n image for what it looks like.

 

[Joe4]

The problem I am running into/the question that I have is that once a user clicks the end time button, their name and date is automatically populated in the next row down. Is there a way that I can make it so their name/date populates once the task is chosen? I've upload n image for what it looks like.

You would use a "Worksheet_Change" event procedure, which is VBA code that automatically updates when a certain range is manually updated.
So, you would have VBA code that waits for the Task column to be updated, and then update those other columns.

This is similar to "Time Stamp" procedures people create.
Here is an example of one I did for someone else: Copy & Paste with a VBA TimeStamp

If you need help setting it up, please let me know which column letter the "Task" field is in, and what column letters the fields you want to update are in.

 

[alexwoje]

Thank you for the explanation and I would love some help setting it up. The Task field is in column D. The date is in column B and the username is in column C.

 

[Joe4]

OK, you would remove the sections of your current code that update these cells.
Then, you would add the following code to the appropriate Sheet module.
The easiest way to get there is to go to the Sheet that you wish to apply this to, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops up.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range, cell As Range
    
    Set rng = Intersect(Target, Range("D:D"))
    
    If rng Is Nothing Then Exit Sub
    
'   Loop through each cell in range
    For Each cell In rng
'       Only update if not first row, and cell in column D has data
        If (cell.Row > 1) And (cell.Value <> "") Then
            Application.EnableEvents = False
'           Update column B with current date
            cell.Offset(0, -2) = Date
            cell.Offset(0, -2).NumberFormat = "dd-mmm-yyyy"
'           Update column C with username
            cell.Offset(0, -1) = Application.UserName
            Application.EnableEvents = True
        End If
    Next cell

End Sub

This is automated VBA code that will run automatically anytime you manually update a cell in column D below row 1.

 

[alexwoje]

Thank you!

 

[Joe4]

You are welcome.