Time Macros

[Shylyn]

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?

[#2]

Presumably you're entering the current time via the function NOW(). One possible resolution is to include in the macro the instructions to copy, and then use Paste Special ... Values and Number Formatting to replace the NOW() with the simple value, which won't be updated.

[#3]

@Shylyn 

Please provide the code of the macros, or preferably, attach a copy of the workbook without sensitive data.

[#4]

I am using the NOW() function. I am not copying it for the other buttons, I was adding it to each button individually. I tried the copy/paste and figured that was the issue. I will try the Paste special and see if that works. I know very little about macros and I am trying to replace a workbook for my job.

[#5 · Accepted solution]

@Shylyn

Instead of setting the formula of a cell to "=NOW()", set its value to Now. For example:

Sub SetTime()
    Range("D2").Value = Now
End Sub

.

[#6]

@Shylyn

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.

[#7]

@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

[#8]

THANK YOU SO MUCH! This is working just like I need it to.