Hello,

I'm trying to work on a spreed sheet for tracking vendor calls and what
have you. I would like to make it easy for our workers to insert the
current date and time by means of a mouse click. Currently I have used
validation to give them dropdown lists for varying cell data and would
like something kinda like that for the date/time issue. I know of the
=NOW() function but the issue i have with, when wrapped with validation
at least) is that it needs to be recalculated which means they have to
click the drop down box, select the time and then reclick to post the
current date/time.

I'm sure I will need to use a Macro or VBA but I'm pretty green on both
these topics and would like some input on how I might accomplish said
task. It looks as if VBA is more advanced than a regular macro so I
assume VBA is the way to go.


Thanks ahead of time for all your input.

Mattchewie

You could create a button - either a CommandButton on your sheet or a custom
toolbar button, and set it to run the following code:

Public Sub Button1.Click
ActiveCell.Value = Now()
ActiveCell.NumberFormat = "mm/dd/yy hh:mm"
End Sub

Then NumberFormat part is to make sure the time stamp shows as a date/time
and not as a numeric value, which would look strange - you could adjust the
format there it if you prefer it to be different.

P.S. in the current Excel world macros and VBA are really the same: the
macro recorder simply creates VBA code for you, and thus is easier for users
who do not know how to write code, but you can always edit the code or create
your own. A good way to get started is to record some macros as you do
various things and look at the resulting code - you can learn a lot this way.
But as you learn more VBA you will learn how you can write code that is more
efficient and more adaptable to different situations than you will get with
the macro recorder.

" wrote:

Hello,

I'm trying to work on a spreed sheet for tracking vendor calls and what
have you. I would like to make it easy for our workers to insert the
current date and time by means of a mouse click. Currently I have used
validation to give them dropdown lists for varying cell data and would
like something kinda like that for the date/time issue. I know of the
=NOW() function but the issue i have with, when wrapped with validation
at least) is that it needs to be recalculated which means they have to
click the drop down box, select the time and then reclick to post the
current date/time.

I'm sure I will need to use a Macro or VBA but I'm pretty green on both
these topics and would like some input on how I might accomplish said
task. It looks as if VBA is more advanced than a regular macro so I
assume VBA is the way to go.


Thanks ahead of time for all your input.

Mattchewie

Took a look in the archives

http://groups.google.com/advanced_gr...ugroup=*excel*

Here's one possibility:

http://groups-beta.google.com/group/...misc/browse_fr
m/thread/65f3a0b5357742cf/670b5910f9729c9a?hl=en#670b5910f9729c9a

In article .com,
wrote:

Hello,

I'm trying to work on a spreed sheet for tracking vendor calls and what
have you. I would like to make it easy for our workers to insert the
current date and time by means of a mouse click. Currently I have used
validation to give them dropdown lists for varying cell data and would
like something kinda like that for the date/time issue. I know of the
=NOW() function but the issue i have with, when wrapped with validation
at least) is that it needs to be recalculated which means they have to
click the drop down box, select the time and then reclick to post the
current date/time.

I'm sure I will need to use a Macro or VBA but I'm pretty green on both
these topics and would like some input on how I might accomplish said
task. It looks as if VBA is more advanced than a regular macro so I
assume VBA is the way to go.


Thanks ahead of time for all your input.

Mattchewie

No nacro needed Mattchewie
CTRL+; inserts the current date
CTRL+SHIFT+; inserts the current time

With kind regards,
Ton Teuns

*** Sent via Developersdex http://www.developersdex.com ***

K Dales,

This is pretty much what I was looking for !! thank you!

One thing I did notice is that the timestamp is only placed in when the
cell is active and not in edit mode.

I was wondering if there is a code option to were the user could be
editing the cell and then be able to click this timestamp button to
give the date. I'm trying to educate them in the Ctrl+; and the
Ctrl+shift+; but untill they fully understand I would like for this
button to the so called panic button

let me know if I need to elborate more on that idea (5:24am right now
and not thinking straight lol)

I think I understand; the problem is that I don't know how to get a macro to
run while in edit mode. Edit mode is in effect like a procedure itself that
"runs" and can't be interrupted until it is complete (i.e. the user finishes
entering the changes). There may be some ways to do this that I am not aware
of; you may want to search to see if anyone has asked a question like that
before (perhaps search on Edit Mode).

" wrote:

K Dales,

This is pretty much what I was looking for !! thank you!

One thing I did notice is that the timestamp is only placed in when the
cell is active and not in edit mode.

I was wondering if there is a code option to were the user could be
editing the cell and then be able to click this timestamp button to
give the date. I'm trying to educate them in the Ctrl+; and the
Ctrl+shift+; but untill they fully understand I would like for this
button to the so called panic button

let me know if I need to elborate more on that idea (5:24am right now
and not thinking straight lol)