I have an equation that calculates elapsed time. You click on a button and it inputs the current time in the cell below it. Then in the cell to the right of the input value is a cell with the equation that calculates the remaining time.
The Macro for the Button is:
Sub Sub_Timestamp1()
Range("I4:I4").Select
Range("I4:I4").Value = Time()
End Sub
The function for the cell is:
=IF(I4="","",(NOW()-ROUNDDOWN(NOW(),0))-I4)
I would like to be able to Delete or Add Columns and Rows left and above the location where the Button and the cell that it puts the time into and have both update without having to change the VBA code to the new range location every time.
I've used the ActiveCell.Value command before but I don't want to force the user to click on the cell below the button every time. I've played with the idea of adding a unique set of letters (or a code) in the cell directly under the Button and one row above the cell that the button inputs the time into. Then... (some how) use the match() function to find the range of the cell containing the code and basing the Button input location on that range.
I'd also like to use a Command Button (ActiveX Control) rather than a Button (Form Control) and associate it with a macro.
Any help would be great. Thanks in advance.
The Macro for the Button is:
Sub Sub_Timestamp1()
Range("I4:I4").Select
Range("I4:I4").Value = Time()
End Sub
The function for the cell is:
=IF(I4="","",(NOW()-ROUNDDOWN(NOW(),0))-I4)
I would like to be able to Delete or Add Columns and Rows left and above the location where the Button and the cell that it puts the time into and have both update without having to change the VBA code to the new range location every time.
I've used the ActiveCell.Value command before but I don't want to force the user to click on the cell below the button every time. I've played with the idea of adding a unique set of letters (or a code) in the cell directly under the Button and one row above the cell that the button inputs the time into. Then... (some how) use the match() function to find the range of the cell containing the code and basing the Button input location on that range.
I'd also like to use a Command Button (ActiveX Control) rather than a Button (Form Control) and associate it with a macro.
Any help would be great. Thanks in advance.