Time Tracker - Script / Macro --- Help?

M

mrb89

New Member
Joined
Apr 20, 2022
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
Hi,

I will be sharing this file with my coworkers to track the time we spent on each task. I found some problems with the script below.
1) If I want to delete a specific row - and try to continue adding data and using the macro to track the time spent on a task, the time is inserted on the row below the deleted row - and not actually on the actual "new" row.
2) Every time that I click on the Clear Button (btn_clear) it clears ALL the data. - I added a similar function "btn_clearRow()" to delete the current row - but something is missing and I can't get it to work.


FYI I have headers, and the data (task name, task description - start time, end time, etc...) the data - specifically the start time/end time - will be recorded on Column F&G - Row 8.

How should I update the script to fix the 2 problems mentioned above?
Could a function be also added so that it requires the person to select a task (column D) before using the automated time tracker?

Thank you!


SCRIPT:

Rich (BB code): 
function btn_clear() {
  var spreadsheet = SpreadsheetApp.getActive();
  var ui = SpreadsheetApp.getUi();
  var userChoice = ui.alert('STOP! Are you sure you want to clear all DATA?', ui.ButtonSet.OK_CANCEL);
 
  if (userChoice == ui.Button.OK) {
     spreadsheet.getRange('AA1').setValue(8);
    spreadsheet.getRange('F8:H' + spreadsheet.getDataRange().getNumRows() +1 ).clearContent();
   }
};
function btn_clearROW() {
  var spreadsheet = SpreadsheetApp.getActive();
  var ui = SpreadsheetApp.getUi();
  var userChoice = ui.alert('STOP! DELETE CURRENT ROW??', ui.ButtonSet.OK_CANCEL);
  
  if (userChoice == ui.Button.OK) {
     spreadsheet.getRange('CurrentRow').setValue(0);
    spreadsheet.getRange('CurrentRow' + spreadsheet.getDataRange().getNumRows() +1 ).clearContent();
   }
};
//THIS BUTTON IS PRESSED WHEN WE START THE PROCESS
function btn_start() {
  var spreadsheet = SpreadsheetApp.getActive();
 
  if (spreadsheet.getRange('AA1').getValue() == '' ) { 
      SpreadsheetApp.getUi().alert('Click on Clear Button First');
  }
  else {
    var CurrentRow = spreadsheet.getRange('AA1').getValue();
    spreadsheet.getRange('F' + CurrentRow).setValue(new Date());
    spreadsheet.getRange('F' + CurrentRow).setNumberFormat('h:mm:ss');
  }     
};
//THIS BUTTON IS PRESSED WHEN WE END THE PROCESS
function btn_stop() {
  var spreadsheet = SpreadsheetApp.getActive();
  var CurrentRow = spreadsheet.getRange('AA1').getValue();
 
  if (CurrentRow == '' ) { 
    SpreadsheetApp.getUi().alert('Click on Clear Button First');
  }
 
  else if ( spreadsheet.getRange('F' + CurrentRow).getValue() == '' ){
      SpreadsheetApp.getUi().alert('Click on Start Button first');
    }
 
  else{
    if ( spreadsheet.getRange('F' + CurrentRow).getValue() != '' )  {
      spreadsheet.getRange('G' + CurrentRow).setValue(new Date());
      spreadsheet.getRange('G' + CurrentRow).setNumberFormat('h:mm:ss');
      spreadsheet.getRange('H' + CurrentRow).setValue('=text(G' + CurrentRow + '-F' + CurrentRow + ', "hh:mm:ss")');
      spreadsheet.getRange('AA1').setValue(CurrentRow + 1 ) ;
    }
  }
 
};
//THIS BUTTON IS USED TO RECORD ENDTIME AND START TIME IN CONTIUATION
function btn_split() {
 
  //first we stop the process
  var spreadsheet = SpreadsheetApp.getActive();
  var CurrentRow = spreadsheet.getRange('AA1').getValue();
   
  if (CurrentRow == '' ) { 
    SpreadsheetApp.getUi().alert('Click on Clear Button First');
  }
  else if ( spreadsheet.getRange('F' + CurrentRow).getValue() == '' ){
      SpreadsheetApp.getUi().alert('Click on Start Button first');
    }
  
    else{
      spreadsheet.getRange('G' + CurrentRow).setValue(new Date());
      spreadsheet.getRange('G' + CurrentRow).setNumberFormat('h:mm:ss');
      spreadsheet.getRange('H' + CurrentRow).setValue('=text(C' + CurrentRow + '-B' + CurrentRow + ', "hh:mm:ss")');
      CurrentRow ++;
      spreadsheet.getRange('AA1').setValue(CurrentRow) ;
     
      //Start Time
      spreadsheet.getRange('F' + CurrentRow).setValue(new Date());
      spreadsheet.getRange('F' + CurrentRow).setNumberFormat('h:mm:ss');
    
  }
 
};
function UntitledMacro() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('F8').activate();
};
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
may this can help

VBA Code: 
Option Explicit

Private Sub btn_start_Click()
    Dim lastRow As Long
    lastRow = WorksheetFunction.Max(8, WorksheetFunction.CountA(Range("D:D")) + 7)
    With Worksheets("Sheet1")
        If Trim(.Cells(lastRow, "D").Value) = "" Then
            MsgBox "Please enter a task name before starting the timer."
            Exit Sub
        End If
        .Cells(lastRow, "F").Value = Time
        .Cells(lastRow, "F").NumberFormat = "h:mm:ss"
    End With
End Sub

Private Sub btn_stop_Click()
    Dim lastRow As Long
    lastRow = WorksheetFunction.Max(8, WorksheetFunction.CountA(Range("D:D")) + 7)
    With Worksheets("Sheet1")
        If Trim(.Cells(lastRow, "D").Value) = "" Then
            MsgBox "Please enter a task name before stopping the timer."
            Exit Sub
        End If
        If Trim(.Cells(lastRow, "F").Value) = "" Then
            MsgBox "Please start the timer before stopping it."
            Exit Sub
        End If
        .Cells(lastRow, "G").Value = Time
        .Cells(lastRow, "G").NumberFormat = "h:mm:ss"
    End With
End Sub

Private Sub btn_clearRow_Click()
    Dim lastRow As Long, currRow As Long
    currRow = ActiveCell.Row
    lastRow = WorksheetFunction.Max(8, WorksheetFunction.CountA(Range("D:D")) + 7)
    If currRow < 8 Or currRow > lastRow Then
        MsgBox "Please select a task before clearing the row."
        Exit Sub
    End If
    With Worksheets("Sheet1")
        .Cells(currRow, "D").Value = ""
        .Cells(currRow, "E").Value = ""
        .Cells(currRow, "F").Value = ""
        .Cells(currRow, "G").Value = ""
    End With
End Sub

Private Sub btn_clear_Click()
    Dim lastRow As Long
    lastRow = WorksheetFunction.Max(8, WorksheetFunction.CountA(Range("D:D")) + 7)
    If MsgBox("Are you sure you want to clear all data?", vbYesNo) = vbNo Then Exit Sub
    With Worksheets("Sheet1")
        .Range("D8:G" & lastRow).ClearContents
    End With
End Sub
 
Upvote 0
rakesh seebaruth said:
may this can help

VBA Code: 
Option Explicit

Private Sub btn_start_Click()
    Dim lastRow As Long
    lastRow = WorksheetFunction.Max(8, WorksheetFunction.CountA(Range("D:D")) + 7)
    With Worksheets("Sheet1")
        If Trim(.Cells(lastRow, "D").Value) = "" Then
            MsgBox "Please enter a task name before starting the timer."
            Exit Sub
        End If
        .Cells(lastRow, "F").Value = Time
        .Cells(lastRow, "F").NumberFormat = "h:mm:ss"
    End With
End Sub

Private Sub btn_stop_Click()
    Dim lastRow As Long
    lastRow = WorksheetFunction.Max(8, WorksheetFunction.CountA(Range("D:D")) + 7)
    With Worksheets("Sheet1")
        If Trim(.Cells(lastRow, "D").Value) = "" Then
            MsgBox "Please enter a task name before stopping the timer."
            Exit Sub
        End If
        If Trim(.Cells(lastRow, "F").Value) = "" Then
            MsgBox "Please start the timer before stopping it."
            Exit Sub
        End If
        .Cells(lastRow, "G").Value = Time
        .Cells(lastRow, "G").NumberFormat = "h:mm:ss"
    End With
End Sub

Private Sub btn_clearRow_Click()
    Dim lastRow As Long, currRow As Long
    currRow = ActiveCell.Row
    lastRow = WorksheetFunction.Max(8, WorksheetFunction.CountA(Range("D:D")) + 7)
    If currRow < 8 Or currRow > lastRow Then
        MsgBox "Please select a task before clearing the row."
        Exit Sub
    End If
    With Worksheets("Sheet1")
        .Cells(currRow, "D").Value = ""
        .Cells(currRow, "E").Value = ""
        .Cells(currRow, "F").Value = ""
        .Cells(currRow, "G").Value = ""
    End With
End Sub

Private Sub btn_clear_Click()
    Dim lastRow As Long
    lastRow = WorksheetFunction.Max(8, WorksheetFunction.CountA(Range("D:D")) + 7)
    If MsgBox("Are you sure you want to clear all data?", vbYesNo) = vbNo Then Exit Sub
    With Worksheets("Sheet1")
        .Range("D8:G" & lastRow).ClearContents
    End With
End Sub
Click to expand...
Hi Rakesh,

I tried using the script you sent me but I keep getting this message (screenshot below) when I click the "ROW" button.
Also, the start button doesn't show the message "MsgBox "Please enter a task name before starting the timer."


1675190610745.png
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
I need help with VBA/macro script to collect user input and fill cells
Replies
4
Views
502
mumps
mumps
S
  • Question Question
Office Scripts Optimization
Replies
0
Views
1K
shaunreeder
S
E
  • Question Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
525
offthelip
O
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
J
  • Question Question
Help with using Macros to Format/Replace
Replies
0
Views
461
jarod_007
J

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top