End time button gets Inactive when workbook closed and reopened

[sumit dey]

Hello Everyone,

I have created a small MIS for my team which have a start Time and End Time Buttons to capture productive Time.

After I click the Start Time Button start time gets capture in the defined range of cells and Endtime Button Gets active to capture end time when clicked. But, if I close the Workbook without clicking the End Time Button and re open the workbook the End Time button gets Inactive and I have to click the Start Button once again and therefore inaccurate data gets captured.

I am using the below code:

in Sheet1



Const ms PASSWORD as String ="ThePassword"

Private Sub btnStart_Click()
Sheet1.Unprotect Password:= ms PASSWORD
Cells(Rows.Count, 4).End(xlup).Offset(1)=Date
Cells(Rows.Count, 4).End(xlup).NumberFormat="dd mmm yyyy"
Cells(Rows.Count, 8).End(xlup).Offset(1)=Now
Cells(Rows.Count, 8).End(xlup).NumberFormat="hh:mm"
Cells(Rows.Count, 3).End(xlup).Offset(1) = Environ("username")
Me.btnStart=False
Me.btnStop=True
Sheet1.Protect Password:=msPASSWORD
End Sub

Private Sub btnStop_Click()
Sheet1.Unprotect Password:=msPASSWORD
Cells(Rows.Count, 9).End(xlup).Offset(1)=Now
Cells(Rows.Count, 9).End(xlup).NumberFormat="hh:mm"
Me.btnStart=True
Me.btnStop=false
Sheet1.Protect Password:=msPASSWORD
End Sub


In ThisWorkBook:



Private Sub WorkBook_Open()
Sheets(1).btnStart.Enabled=True
Sheets(1).btnStop.Enabled=false
End Sub


I want the end time button to remain active inspite of the workbook is closed till it's clicked...

One more thing, is there a way wherein I just click the Start button once and whenever I click the End time button it capture the end time as well as the next start time in respective cells so, that I just required to click a single button (End Time Button) each time to record my productive hours...

thanks in advance everyone...

[SamT]

I think you missed a bunch of code.

In any case, in the Workbook_BeforeClose sub, run the sub assigned to the EndTime button

[sumit dey]

I tried but, it isn't working....any idea it shows error related variables and MSPASSWORD then the Me.btnStart_Click line....
any idea

[SamT]

Replace:

Private Sub btnStop_Click()
Sheet1.Unprotect Password:=msPASSWORD
Cells(Rows.Count, 9).End(xlup).Offset(1)=Now
Cells(Rows.Count, 9).End(xlup).NumberFormat="hh:mm"
Me.btnStart=True
Me.btnStop=False
Sheet1.Protect Password:=msPASSWORD
End Sub
With:

Private Sub btnStop_Click()
StopButtonCode
End Sub
and

Private Sub StopButtonCode()
Sheet1.Unprotect Password:=msPASSWORD
Cells(Rows.Count, 9).End(xlup).Offset(1)=Now
Cells(Rows.Count, 9).End(xlup).NumberFormat="hh:mm"
Me.btnStart=True
Me.btnStop=False
Sheet1.Protect Password:=msPASSWORD
End Sub

Edit to match:

Private Sub Workbook_BeforeClose(Cancel as Boolean)
StopButtonCode
End Sub

[sumit dey]

Hi SamT,
Thanks for all the help you have provided.

I have tried your code above but, each time it says that 'a sub or function not defined" I have also defined a variable with StopButtonCode and also tried giving other names but, each time it showed an error Ambiguos name and another error says "Expected procedure, not variable"

[SamT]

All this code only uses the start button. You can delete the stop button. Try it. Click the start button a few times, close the book. Open the book. Repeat a few tmes

Put these subs in the ThisWorkbook Code page

Private Sub Workbook_Open()
StartCode "Workbook Opened"
With Sheets(" Button sheet name here") '<<<<<<<<<<<<<<<<
.btnStart = True
.btnStart.Caption = "Start Task"
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopCode "Workbook Closed"
End Sub
Put these subs in the Worksheet Code page

Option Explicit

Const msPASSWORD As String = "ThePassword"

Private Sub btnStart_Click()
Sheet1.Unprotect Password:=msPASSWORD

If btnStart = True Then
StartCode Environ("username")
btnStart.Caption = "Stop Task"
Else
StopCode
btnStart.Caption = "Start Task"
End If
btnStart = Not btnStart

Sheet1.Protect Password:=msPASSWORD
End Sub

Public Sub StartCode(Str As String)
Dim Rw As Long
Rw = Cells(Rows.Count, 3).End(xlUp).Row
If Cells(Rw, 3) = "Workbook Closed" Then
Rw = Rw + 2
Else
Rw = Rw + 1
End If

With Rows(Rw)
Cells(3) = Str
Cells(4) = Format(Date, "dd mmm yyyy")
Cells(8) = Format(Time, "hh:mm")
End With
End Sub

Public Sub StopCode(Optional Str As String)
Dim Rw As Long
Rw = Cells(Rows.Count, 3).End(xlUp).Row
If Str = "Workbook Closed" Then
Cells(Rw, 9) = Format(Time, "hh:mm")
Rw = Rw + 1
Cells(Rw, 3) = Str
End If
Cells(Rw, 9) = Format(Time, "hh:mm")
End Sub