click button to display time

[chriswrcg]

I am trying to build a time study sheet.
I am placing a button in cell A1 on a worksheet. when that button is clicked it needs to display the time in B1.
That time can not change and should be a simple recording of when the button was clicked.

I have tried various forms of the =now() formula but could use some help.

[chriswrcg]

Playing around with it more i have been able to create a button that when clicked will display the time in the active cell on the worksheet. I would like to assign a particular cell in which to have the time displayed instead of just the active cell. How do i code what cell to display in?

[dflak]

Obviously, you are starting on the road of macros! Let's get you off to a good start then.

The first thing to do is open the macro editor and insert a module. Then go to Tools -> Options and Check off Auto Syntax Check and Check on Require Variable Declaration an OK the results. You will probably want to keep these settings for the rest of your VB career.

If Auto Syntax Check is left on, and you are typing a line of code and you decide you want to copy and paste some code from somewhere else, you will get a dialog box when you leave the line you are typing on stating that there is a syntax error. You have to clear this box. With the box unchecked, the line will turn red to indicate that there is a syntax error on it. You won't have to make the extra clicks.

Checking on Require Variable Declaration puts the words "Option Explicit" on the top of every module. This means that you MUST define variables before using them. This saves a lot of heartache due to misspellings. So XY0l and XYO1 when you really mean XY01 will be detected and flagged as undefined variables. Otherwise, they will take on a null value and you'll wonder why you are not getting the answer you are expecting.

One other tip: what you think the active sheet or active cell is may be different than what excel thinks the active sheet or active cell is. Always tell excel where you want to be. The code below does this.

The macro recorder is a good tool. It will give you an idea on how to do things and it will give you syntax to tell you how to read and set things. However, it has a nasty habit that you can clean up. It likes to produce code like:

Please Login or Register  to view this content.

When you see something like this, you can almost always replace it with:

Please Login or Register  to view this content.

Finally, the code below uses a shortcut for the sheet name. In this code it's called sh. You can "point" the shortcut to a sheet by setting it using:

Please Login or Register  to view this content.

This will keep you from having to type Sheets("YourSheetName") all over the place. So instead of referencing a cell like Sheets("YourSheetName").Range("A1") you can simply type sh.Range("A1").

Learn this trick now and it will help a lot when you get to more advanced coding. When you set a sheet, it not only remember its name, it also remembers what workbook it is in. So if you have multiple workbooks open, it will keep you straight.

So much for the big tips. Here is the code to put the current time in a specific cell.

Please Login or Register  to view this content.

Oh, and one more thing. Get in the habit of using consistent naming conventions. For example I use sh for a sheet. Some people use ws for worksheet. I use things like shD for a data sheet and shR for a report sheet. It makes more sense than sh1 and sh2. I always reserve i and j as indices for rows and columns respectively. That goes back to my FORTRAN days.

[chriswrcg]

How would I change the format of the display to just hh:mm:ss ?

[dflak]

I recorded the following using the macro recorder:

Please Login or Register  to view this content.

Translating this into the code yields"

Please Login or Register  to view this content.

[chriswrcg]

how can i move the time result down one cell each time the button is clicked? start in D3, go to D4 and son on. I know that the string
Selection.Offset(1, 0).Select will move a selection down one cell each time the button is clicked but I cant make it work when you start with the particular cell

[DannyJ]

Assuming it wil lstart from the top and always go down to the first empty row this code will be fine.

Please Login or Register  to view this content.

Hope that works for you.

[chriswrcg]

Thanks this really helps.
I do have another question though. If I have multiple buttons and one post to column A, what do I change in the Macro to change button two to column C or D or E or whatever?
I Tried changing ("A:A") to ("D:D") but nothing happened.
It is going to take me a while to get used to writing Macros

[chriswrcg]

Does anyone have any ideas on how to change the display location for this script?