The situation:
I have modified a workbook that contains a stopwatch. This tool is to monitor the downtimes of lines in a factory. It has, now (and I don't plan to create more worksheets): - Info (for explaining how to use it); - Ref (for putting references that may be/will be editable by the user); - StopWatch (Which contain the clock, start/pause/stop+record buttons, and the table to register it); - ghist (A 'ghost history', that will act as a backup for every time recorded ever in that spreadsheet; - Calc (That is an 'imported' table from the StopWatch sheet, so some calculations can be done without screwing the Stopwatch sheet).
How the workbook behave
- Info: doesn't matter, its pure informative;
- Ref: It's the with time reference for the clock, columns with options for the data validation lists; in the cell, A1 is protected to now destroy the reference for the clock;
- Stopwatch: you select some options within the available data validation lists and write (just a speed value) in column C, Line, machine, product, etc. Then you start the clock, with a button, can pause it, with another, and then stop it, to register the passed time. The registering region starts in F3 and ends on the column S. After finishing measuring the data, you can import it to the 'Calc' sheet (I'm just copying the values) and then, if you want to use it again, you can clean the table on the StopWatch sheet, to register new data. In the 'background' of the workbook I, after every STOP clicked, want it to make a copy of that line (the last one on the table of the StopWatch worksheet, the last measurement), as a safety backup.
My Problems:
It is a common one, but after days of trying and error I couldn't figure it out, it is simple, but I can't overcome this. Putting in a list again:
- I managed to 'Frankstein' the code, I can copy the last line and paste it on the next empty row in the backup sheet, but, after I CLEAN the main table of the StopWatch sheet when I start to record the time again, it overwrites the existing lines, starting from the line 2.
- When copied the values are paste in the F column, I want them in column A.
- There are times when I run the macro, in the backup sheet, the code write, copies the last line of the main table and paste in the backup after stopping the clock, if I run it again I copies no the last, but the one before, 1 & 2, then I run it again, it copies 1, 2 & 3, and keep like this.
- If I measure some points, then import to calc, clean the table and then import again, this new import process will overwrite the existing points.
- Could export the calc table to a csv file, f
My Assumptions
- I put everything in one module alone;
- This code is a combination of codes, some notations may not correlate
- I may have used some unnecessary functions/mods, but I tried everything I could to get here
- I commented on every line I could to try to show what I was understanding in that place.
I appreciate ANY help on this. Thanks a lot in advance.
The code
Option Explicit
'Source: https://trumpexcel.com/stopwatch-in-excel/
'-----------------------------------------------------
'Definition of the variable of the time running in the clock
Dim NextTick As Date
Dim t As Date
Dim PreviousTimerValue As Date
Sub Start()
'-----------------------------------------------------
'Unlock the reference cell A1, on Ref Sheet, to be used in the counting of time
Ref.Unprotect Password:="timeref"
'Check the reference time and "free it to run" so the time pass
PreviousTimerValue = Ref.Range("A1").Value
t = Time
Call ExcelStopWatch
'-----------------------------------------------------
End Sub
Private Sub ExcelStopWatch()
'-----------------------------------------------------
'Check the reference time (Cell A1 on Ref), changes the color of the clock acording to the parameters in the Ref sheet
Ref.Range("A1").Value = Format(Time - t + PreviousTimerValue, "hh:mm:ss")
NextTick = Now + TimeValue("00:00:01")
'Green color
If Ref.Range("A1").Value > Ref.Range("B3") And Ref.Range("A1").Value <= Ref.Range("B4") Then
With StopWatch.Shapes("TimeBox")
.Fill.ForeColor.RGB = RGB(0, 255, 0)
End With
'Yellow color
Else
If Ref.Range("A1").Value > Ref.Range("B4") And Ref.Range("A1").Value <= Ref.Range("B5") Then
With StopWatch.Shapes("TimeBox")
.Fill.ForeColor.RGB = RGB(255, 255, 0)
End With
'Red color
Else
If Ref.Range("A1").Value > Ref.Range("B5") Then
With StopWatch.Shapes("TimeBox")
.Fill.ForeColor.RGB = RGB(255, 0, 0)
End With
End If
End If
End If
Application.OnTime NextTick, "ExcelStopWatch"
'-----------------------------------------------------
End Sub
Sub Pause()
'Pause command in the clock
On Error Resume Next
Application.OnTime earliesttime:=NextTick, procedure:="ExcelStopWatch", schedule:=False
End Sub
Sub StopReset()
'-----------------------------------------------------
'Stop command that also will copy the parameters defined on the StopWatch worksheet to the main table
On Error Resume Next
'Reset the background color or the clock to white
With StopWatch.Shapes("TimeBox")
.Fill.ForeColor.RGB = RGB(255, 255, 255)
End With
'Copy commands, to write down every variable:
If StopWatch.Range("F3") = "" And Ref.Range("A1").Value > 0 Then
StopWatch.Range("F3").Value = 1 'To write the order numbers
StopWatch.Range("F3").Offset(0, 1).Value = Ref.Range("A1").Value 'To write the duration
StopWatch.Range("F3").Offset(0, 2).Value = Date + Time 'To write the Date + Time
StopWatch.Range("F3").Offset(0, 3).Value = StopWatch.Range("C15").Value 'To copy the Line Name
StopWatch.Range("F3").Offset(0, 4).Value = StopWatch.Range("C16").Value 'To copy the Machine
StopWatch.Range("F3").Offset(0, 5).Value = StopWatch.Range("C17").Value 'To copy the Product
StopWatch.Range("F3").Offset(0, 6).Value = StopWatch.Range("C18").Value 'To copy the Type of Cover
StopWatch.Range("F3").Offset(0, 7).Value = StopWatch.Range("C19").Value 'To copy the Bottle Size
StopWatch.Range("F3").Offset(0, 8).Value = StopWatch.Range("C20").Value 'To copy the Bottle Model
StopWatch.Range("F3").Offset(0, 9).Value = StopWatch.Range("C21").Value 'To copy the Box Formation
StopWatch.Range("F3").Offset(0, 10).Value = StopWatch.Range("C22").Value 'To copy the Speed of the line
StopWatch.Range("F3").Offset(0, 11).Value = StopWatch.Range("C23").Value 'To copy the Shift
Else:
'To identify the last used row and use the one after that
StopWatch.Range("F2").End(xlDown).Offset(1, 0).Value = StopWatch.Range("F2").End(xlDown).Value + 1 'To write the order numbers
StopWatch.Range("F2").Offset(0, 1).End(xlDown).Offset(1, 0).Value = Ref.Range("A1").Value 'To write the duration
StopWatch.Range("F2").Offset(0, 2).End(xlDown).Offset(1, 0).Value = Date + Time 'To write the Date + Time
StopWatch.Range("F2").Offset(0, 3).End(xlDown).Offset(1, 0).Value = StopWatch.Range("C15").Value 'To copy the Line Name
StopWatch.Range("F2").Offset(0, 4).End(xlDown).Offset(1, 0).Value = StopWatch.Range("C16").Value 'To copy the Machine
StopWatch.Range("F2").Offset(0, 5).End(xlDown).Offset(1, 0).Value = StopWatch.Range("C17").Value 'To copy the Product
StopWatch.Range("F2").Offset(0, 6).End(xlDown).Offset(1, 0).Value = StopWatch.Range("C18").Value 'To copy the Type of Cover
StopWatch.Range("F2").Offset(0, 7).End(xlDown).Offset(1, 0).Value = StopWatch.Range("C19").Value 'To copy the Bottle Size
StopWatch.Range("F2").Offset(0, 8).End(xlDown).Offset(1, 0).Value = StopWatch.Range("C20").Value 'To copy the Bottle Model
StopWatch.Range("F2").Offset(0, 9).End(xlDown).Offset(1, 0).Value = StopWatch.Range("C21").Value 'To copy the Box Formation
StopWatch.Range("F2").Offset(0, 10).End(xlDown).Offset(1, 0).Value = StopWatch.Range("C22").Value 'To copy the Speed of the Line
StopWatch.Range("F2").Offset(0, 11).End(xlDown).Offset(1, 0).Value = StopWatch.Range("C23").Value 'To copy the Shift
End If
'-----------------------------------------------------
'Reset the value of the clock to zero
Application.OnTime earliesttime:=NextTick, procedure:="ExcelStopWatch", schedule:=False
Ref.Range("A1").Value = 0
'-----------------------------------------------------
'Protect the cell of reference for the time, A1 on sheet Ref
Ref.Protect Password:="timeref"
'-----------------------------------------------------
'Backup of use, should copy all the lines that once was "stoped" by the button click, the last line of the main table near the clock
'IDK what this line do, but made the 'flickering' of the screen stop.
Application.ScreenUpdating = False
'Defining the variable to find the last row in the main table and in the ghost history (backup)
Dim lastrowSrc As Long
Dim lastrowDest As Long
'Get last row of data
lastrowSrc = Sheets("StopWatch").Range("F" & Rows.Count).End(xlUp).Row
'Get first blank row (last row of data +1)
lastrowDest = Sheets("gHist").Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy row
Sheets("StopWatch").Range("F3:S" & lastrowSrc).EntireRow.Copy Sheets("ghist").Range("A" & lastrowDest)
'Complement of the code to stop the flickering when copying te line.
Application.CutCopyMode = False
Application.ScreenUpdating = True
'-----------------------------------------------------
End Sub
'-----------------------------------------------------
'To clear the main table of the StopWatch
Sub ResetMT()
'Definying the worksheets I'm working with
Dim ws1 As Worksheet
Dim ws2 As Worksheet
'declare an object variable to hold a reference to cells to clear
Dim LRS_Reset As Long
Dim LRB_Reset As Long
Application.ScreenUpdating = False
'Warning message to confirm exclusion
If MsgBox("These changes cannot be undone. This will erase everything! Are you sure?", vbYesNo) = vbNo Then Exit Sub
'Source of the data
Set ws1 = Sheets("StopWatch")
'Backup Sheet
Set ws2 = Sheets("ghist")
'My tentative to "show" to the stop button the last used row, I think it was 'erasing' the capacity of the code to know the last used row in the backup sheet (ghist)
LRS_Reset = ws1.Range("F" & Rows.Count).End(xlUp).Row + 1
'Comment to myself, learning while making the reset of the main table not erease the headers
'OBS: If you add "+1" after .Row you will keep the headings in case the file is empty. [if it is empty and the code runs without "+1" it will erase the headings]
'Definition of the clearing range, this method keeps the formatting style
Range("F3:S" & LRS_Reset).ClearContents
'Attempt to show the code the last used row in the ghist (backup sheet)
LRB_Reset = ws2.Range("A" & Rows.Count).End(xlUp).Row
'Copied from above to stop the flickering (hope its correct)
Application.CutCopyMode = False
Application.ScreenUpdating = True
'-----------------------------------------------------
End Sub
'-----------------------------------------------------
'To import the captured table for the calculations
Sub ImportMT()
'Warning message to confirm exclusion
If MsgBox("This will import the current data for calculation. Are you sure?", vbYesNo) = vbNo Then Exit Sub
Range("F3", Range("F3").End(xlDown).End(xlToRight)).Copy Worksheets("Calc").Range("A2")
'-----------------------------------------------------
End Sub
'-----------------------------------------------------
'Source: https://stackoverflow.com/questions/10220906/how-to-select-clear-table-contents-without-destroying-the-table
'To clear the content of the calc sheet without losing the headers and style/table
Sub ResetCalcTab()
'Warning message to confirm exclusion
If MsgBox("These changes cannot be undone. This will erase everything on this table! Are you sure?", vbYesNo) = vbNo Then Exit Sub
Application.ScreenUpdating = False
Sheets("Calc").Select
ActiveSheet.ListObjects("Calc").HeaderRowRange.Select
'Remove the filters if one exists.
If ActiveSheet.FilterMode Then
Selection.AutoFilter
End If
'Clear all lines but the first one in the table leaving formulas for the next to go round.
With Worksheets("Calc").ListObjects("Calc")
.Range.AutoFilter
On Error Resume Next
.DataBodyRange.Offset(1).Resize(.DataBodyRange.Rows.Count - 1, .DataBodyRange.Columns.Count).Rows.Delete
.DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
ActiveWindow.SmallScroll Down:=-10000
End With
Application.ScreenUpdating = True
'-----------------------------------------------------
End Sub```
