Create Order Timer in MS Excel Using VBA

A user wants to create an automatic timer in MS Excel. He wants to enter an item in column A. Now in column B a timer should start automatically. If he enters a new item name in another cell in column A another timer should start which should be totally different from the first timer. He wants to be able to do this in the entire column. He also wishes to be able to overwrite and item once the order has been completed. Completed orders are indicated by the time in the column B being set to 0:00 and highlighted with some color being filled inside the cell. We have used yellow but the possibilities are many.
The video here describes how you can create an automatic counter using Excel VBA.

Module Code:

Sub startTimer()
Application.OnTime Now + TimeValue(“00:00:0001”), _
“Decrement_Count_By_1”
End Sub
Sub Decrement_Count_By_1()
Dim x, LastRow
LastRow = ActiveSheet.Range(“B” & Rows.Count).End(xlUp).Row
For x = 1 To LastRow

If ActiveSheet.Cells(x, “A”).Value <> “” And _
ActiveSheet.Cells(x, “B”).Value > 0 Then
ActiveSheet.Cells(x, “B”).Interior.ColorIndex = 0
ActiveSheet.Cells(x, “A”).Interior.ColorIndex = 0
ActiveSheet.Cells(x, “B”).NumberFormat = “m:ss”
ActiveSheet.Cells(x, “B”).Value = ActiveSheet.Cells(x, “B”).Value – (1 / 86400)
LastRow = ActiveSheet.Range(“B” & Rows.Count).End(xlUp).Row
End If

If ActiveSheet.Cells(x, “A”).Value <> “” And _
ActiveSheet.Cells(x, “B”).Value < 0 Or ActiveSheet.Cells(x, “C”).Value = “OK” Or ActiveSheet.Cells(x, “C”).Value = “ok” Then
ActiveSheet.Cells(x, “B”).NumberFormat = “m:ss”
ActiveSheet.Cells(x, “B”).Value = “0:00”
ActiveSheet.Cells(x, “B”).Interior.ColorIndex = 36
End If

LastRow = ActiveSheet.Range(“B” & Rows.Count).End(xlUp).Row
Next
startTimer
End Sub

ThisWorkbook code:
Private Sub Workbook_Open()
startTimer
End Sub

Sheet1 code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 1 Then
If Target.Interior.ColorIndex = 36 And _
Target.Offset(0, 1).Value = 0 Then
Target.Interior.ColorIndex = 0
Target.Offset(0, 1).Value = “”
Target.Offset(0, 1).Interior.ColorIndex = 0
End If
If Target.Value <> “” Then
Target.Offset(0, 1).Interior.ColorIndex = 0
Target.Offset(0, 1).NumberFormat = “m:ss”
Target.Offset(0, 1).Value = “00:03:00.00”
End If
End If

Watch the video:

 


Leave a Reply

Your email address will not be published. Required fields are marked *