S
SJC
I am working on a worksheet that uses three buttons to record current date,
start time, and stop time. I have it set up so that the button fills in the
value in the cell to the right of the button in 1 row. I then have a formula
that will calculate the time spent on the activity. These buttons and macros
are all functioning fine for row 1. These are the macros I am using for the
date button, start, and stop buttons.
Public Sub Date1()
Range("C7").Select
With ActiveCell
.Value = Now
.NumberFormat = "mm dd yyyy"
End With
End Sub
Sub StartButton1()
Sheets("sheet1").Select
Range("F7").Select
ActiveCell.FormulaR1C1 = Time()
Sheets("sheet1").Select
ActiveSheet.Activate
End Sub
Sub Stopbutton1()
Sheets("sheet1").Select
Range("H7").Select
ActiveCell.FormulaR1C1 = Time()
Sheets("sheet1").Select
ActiveSheet.Activate
End Sub
Public Sub Date2()
Range("C7").Offset(1, 0).Select
With ActiveCell
.Value = Now
.NumberFormat = "mm dd yyyy"
End With
End Sub
However, I am having two problems:
1. How do I fill down the macro to a number of rows so that the date/time
will automatically fill into the cell to the right of the button? I don't
want the new date to overwrite the date in C7.
2. Also, this spreadsheet will be used to track the 3 hours that are spent
on the activity. I calculated the time using this formula, =L7-SUM(H7-F7).
L7 is the starting point of 3 hours. In each row, I have this formula
=J7-SUM(H8-F8) to continue to track from the 3 hours. This all works fine.
However, when a new date is entered. I would like the 3 hours to start over.
Is there a way to write this formula to all rows that will restart at
L7(3:00) when a new date is entered in column C.
Thanks for any assistance.
start time, and stop time. I have it set up so that the button fills in the
value in the cell to the right of the button in 1 row. I then have a formula
that will calculate the time spent on the activity. These buttons and macros
are all functioning fine for row 1. These are the macros I am using for the
date button, start, and stop buttons.
Public Sub Date1()
Range("C7").Select
With ActiveCell
.Value = Now
.NumberFormat = "mm dd yyyy"
End With
End Sub
Sub StartButton1()
Sheets("sheet1").Select
Range("F7").Select
ActiveCell.FormulaR1C1 = Time()
Sheets("sheet1").Select
ActiveSheet.Activate
End Sub
Sub Stopbutton1()
Sheets("sheet1").Select
Range("H7").Select
ActiveCell.FormulaR1C1 = Time()
Sheets("sheet1").Select
ActiveSheet.Activate
End Sub
Public Sub Date2()
Range("C7").Offset(1, 0).Select
With ActiveCell
.Value = Now
.NumberFormat = "mm dd yyyy"
End With
End Sub
However, I am having two problems:
1. How do I fill down the macro to a number of rows so that the date/time
will automatically fill into the cell to the right of the button? I don't
want the new date to overwrite the date in C7.
2. Also, this spreadsheet will be used to track the 3 hours that are spent
on the activity. I calculated the time using this formula, =L7-SUM(H7-F7).
L7 is the starting point of 3 hours. In each row, I have this formula
=J7-SUM(H8-F8) to continue to track from the 3 hours. This all works fine.
However, when a new date is entered. I would like the 3 hours to start over.
Is there a way to write this formula to all rows that will restart at
L7(3:00) when a new date is entered in column C.
Thanks for any assistance.