VBA to start a stop watch vba upon opening and stop vba on close?

[bdbly]

DBly 07:28 PM Today
Hello,

I have a stop watch in my excel file that relies on a start/stop button. I'm trying to create a VBA to click the command button (Start Timer) when the Workbook is opened and click the button (Stop Timer) and save when the excel file is closed.

The stop watch VBA I'm using is attached and can be found here:

https://onedrive.live.com/?id=863B7D...3B7DD7364138EC

Thanks fro the help!
B

 

[gallen]

Hello, I can't view links on my work's pc.

However you need to open the VBA window by pressing ALt + [F11]

Then on the left you should see the project explorer. Double click on "ThisWorkbook" which will open the code window for the workbook.

Now you just need to enter the code you want to run every time the workbook opens and every time it closes:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'Code to run before workbook is closed goes here
End Sub


Private Sub Workbook_Open()
    'Code to run when workbook opens goes here
End Sub

I can't be more specific as you omit the code but obviously you need to call the correct start and stop subs within the open/close subs

 

[Fluff]

Cross posted here
https://www.excelforum.com/excel-pr...d-click-a-button-when-closed.html#post4816679
@bdbly
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[bdbly]

Sorry about the cross post.

Below is the code to the stop watch. I tried Private Sub Workbook_Open() but it creates errors.

Thanks for the help!

Public StopIt As BooleanPublic ResetIt As Boolean
Public LastTime


Private Sub CommandButton1_Click()
    Dim StartTime, FinishTime, TotalTime, PauseTime
    StopIt = False
    ResetIt = False
    If Range("C2") = 0 Then
        StartTime = Timer
        PauseTime = 0
        LastTime = 0
    Else
        StartTime = 0
        PauseTime = Timer
    End If
StartIt:
    DoEvents
    If StopIt = True Then
        LastTime = TotalTime
        Exit Sub
    Else
        FinishTime = Timer
        TotalTime = FinishTime - StartTime + LastTime - PauseTime
        TTime = TotalTime * 100
        HM = TTime Mod 100
        TTime = TTime \ 100
        hh = TTime \ 3600
        TTime = TTime Mod 3600
        MM = TTime \ 60
        SS = TTime Mod 60
        Range("D2").Value = Format(hh, "00") & ":" & Format(MM, "00") & ":" & Format(SS, "00") & "." & Format(HM, "00")
        If ResetIt = True Then
            Range("D2") = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00")
            LastTime = 0
            PauseTime = 0
            End
        End If
        GoTo StartIt
    End If
End Sub


Private Sub CommandButton2_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  Dim j As Range
  StopIt = True
  Range("J2").Value = Range("J2").Value + Range("D2").Value
End Sub


Private Sub CommandButton3_Click()
    Dim Lastrow As Integer
    Dim LLastrow As Integer
    LLastrow = ActiveSheet.Cells(Rows.Count, "L").End(xlUp).Row + 1
    Lastrow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
       Range("L" & LLastrow).Value = Range("J2").Value
       Range("D2, J2").Value = Format(0, "00") & ":" & _
             Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00")
       LastTime = 0
       Range("B2:B" & Lastrow).ClearContents
    ResetIt = True
End Sub


Private Sub CommandButton4_Click()
Dim Lastrow As Integer
Lastrow = ActiveSheet.Cells(Rows.Count, 12).End(xlUp).Row
Range("L2:L" & Lastrow).ClearContents
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo endit
    If Target.Address = "$J$2" Then
        Application.EnableEvents = False
        j = Cells(Cells.Rows.Count, "B").End(xlUp).Row + 1
        Cells(j, 2) = Range("D2").Value
    End If
endit:
Application.EnableEvents = True
End Sub

 

[RoryA]

Put the code from each button into two new routines in a normal module, and then call those routines from the button click events and from the workbook_open and workbook_beforeclose events. Note that the two workbook events must be in the ThisWorkbook module and not in the worksheet module where your button code is now.

 

[bdbly]

Thanks RoryA but would you mind giving me a litet atle more detail. I'm new to VBA and not sure where to start. I'm familiar with ThisWorkbook and Sheet Objects. Just not sure where/format to paste the code. There is a Start Timer (CommandButton1), Stop Timer (CommandButton2), Reset (CommandButton3) and Clear Log Button (CommandButton4) in Sheet1 (Clock). Do I need to remove all the code from Sheet1 (Clock)?

 

[RoryA]

In a new normal module, put this:

Public StopIt As Boolean
Public ResetIt As Boolean
Public LastTime

Sub StartTimer
    Dim StartTime, FinishTime, TotalTime, PauseTime
    StopIt = False
    ResetIt = False
    If Sheet1.Range("C2") = 0 Then
        StartTime = Timer
        PauseTime = 0
        LastTime = 0
    Else
        StartTime = 0
        PauseTime = Timer
    End If
StartIt:
    DoEvents
    If StopIt = True Then
        LastTime = TotalTime
        Exit Sub
    Else
        FinishTime = Timer
        TotalTime = FinishTime - StartTime + LastTime - PauseTime
        TTime = TotalTime * 100
        HM = TTime Mod 100
        TTime = TTime \ 100
        hh = TTime \ 3600
        TTime = TTime Mod 3600
        MM = TTime \ 60
        SS = TTime Mod 60
        Sheet1.Range("D2").Value = Format(hh, "00") & ":" & Format(MM, "00") & ":" & Format(SS, "00") & "." & Format(HM, "00")
        If ResetIt = True Then
            Sheet1.Range("D2") = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00")
            LastTime = 0
            PauseTime = 0
            End
        End If
        GoTo StartIt
    End If
End Sub

Sub StopTimer
  StopIt = True
  Sheet1.Range("J2").Value = Sheet1.Range("J2").Value + Sheet1.Range("D2").Value
End Sub

Now replace all your worksheet code with just this:

Private Sub CommandButton1_Click()
   StartTimer
End Sub


Private Sub CommandButton2_Click()
  StopTimer
End Sub


Private Sub CommandButton3_Click()
    Dim Lastrow As Integer
    Dim LLastrow As Integer
    LLastrow = ActiveSheet.Cells(Rows.Count, "L").End(xlUp).Row + 1
    Lastrow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
       Range("L" & LLastrow).Value = Range("J2").Value
       Range("D2, J2").Value = Format(0, "00") & ":" & _
             Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00")
       LastTime = 0
       Range("B2:B" & Lastrow).ClearContents
    ResetIt = True
End Sub


Private Sub CommandButton4_Click()
Dim Lastrow As Integer
Lastrow = ActiveSheet.Cells(Rows.Count, 12).End(xlUp).Row
Range("L2:L" & Lastrow).ClearContents
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo endit
    If Target.Address = "$J$2" Then
        Application.EnableEvents = False
        j = Cells(Cells.Rows.Count, "B").End(xlUp).Row + 1
        Cells(j, 2) = Range("D2").Value
    End If
endit:
Application.EnableEvents = True
End Sub

And finally in the ThisWorkbook module, add this:

Private Sub Workbook_Open()
StartTimer
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    StopTimer
End Sub

 

[bdbly]

Thanks RoryA!