Aug 16 2021 06:13 AM
I am trying to create a time sheet kinda workbook. I am wanting to set it up that when you click a button it will enter the current time. I need multiple buttons to do this to enter breaks, lunch, etc. When I add macros for the time, clicking one button changes all the times. How can I fix this?
Aug 16 2021 06:18 AM
Aug 16 2021 06:19 AM
Please provide the code of the macros, or preferably, attach a copy of the workbook without sensitive data.
Aug 16 2021 06:25 AM
Aug 16 2021 06:51 AM
SolutionInstead of setting the formula of a cell to "=NOW()", set its value to Now. For example:
Sub SetTime()
Range("D2").Value = Now
End Sub
.
Aug 16 2021 07:39 AM
It is not very clear to me exactly what you want. ``It will enter the current time`` where?! ``I need multiple buttons to do this to enter breaks, lunch, etc`` why?!
And finally: ``Clicking one button changes all the times. How can I fix this?`` How?! We cannot fix what we cannot see.
Without clarity, any answer that you get is probably GIGO.
In any case, you probably do not want to use Excel NOW or VBA Now directly. The first is accurate to the 10 millisecond(!). The second is accurate to the 1 second.
That accuracy will probably screw up any time arithmetic that you might intend to do, which I presume should be accurate only to the 1 minute.
Any VBA solution should probably be of the form:
Sub mytime()
Selection = Format(Now, "h:m")
End Sub
and properly format the selected cell as you prefer.
Caveat: Be wary of any proposed solution that involves multiple calls to Excel NOW or VBA Now; e.g. Timeserial(Hour(Now),Minute(Now),0). The time value (Now) might change between calls, resulting in inconsistencies; but only intermittently (i.e. rarely), which will drive you nuts.
Aug 16 2021 10:48 AM
@Joe User Sorry I am new at using forums like this. I am not sure how to share the workbook so you cab see what i am tryingto accomplish
Aug 16 2021 06:51 AM
SolutionInstead of setting the formula of a cell to "=NOW()", set its value to Now. For example:
Sub SetTime()
Range("D2").Value = Now
End Sub
.