Creating a Time elapsed Spread Sheet that records and posts time...

**swade730** · 11-24-2013, 02:42 PM

Hello,

I am trying to figure out whether or not it is possible to create a macro that would allow a user to click on a button...

We'll call this button "Start Timing"
at the time that this button is clicked, I want VBA to post that time in a cell in a column Labeled Start.
for example...

If I clicked on "Start Timing" at 10:30 am then the first entry would read 10:30 am respectively.

The other button would read..."Stop timing"
And whatever time it happens to be when I click stop timing should reflect in the next column labeled Finish.
So If I stopped timing at 10:45 am then the spread sheet would look something like this...

Start Finish
10:30a 10:45a

and the next time I click on Start... the time would begin to record in the next row...etc...

Finally in another module...I would like to have it where I can email the results on the spreadsheet.

Any help or guidance would be much appreciated. -Thank You

**mehmetcik** · 11-24-2013, 04:37 PM

The first two parts are easy

Right Click on the attached sheet to run the macro.

Right click on the sheet name at the bottom of excel to see the code.

Sorry I don't code for Outlook. I have no interest in that.


Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
LR = Selection.SpecialCells(xlCellTypeLastCell).Row

If Hour(Now) > 12 Then
DisplayTime = Now - 0.5
TimePostFix = " pm"
Else
DisplayTime = Now
TimePostFix = " am"
End If

If Cells(LR, 2).Value = "" Then
Cells(LR, 2).Value = "Stop Time: " & Format(DisplayTime, "hh:mm:ss") & TimePostFix
Else
LR = LR + 1
Cells(LR, 1).Value = "StartTime: " & Format(DisplayTime, "hh:mm:ss") & TimePostFix

End If
    Range("B1").Value = "=NOW()"
    Columns("A:B").Select
    Selection.Columns.AutoFit
    Cells(LR, 1).Select


Cancel = True
End Sub