Record Date and Time Macro

**nsorden** · 07-01-2009, 02:02 PM

I have already looked at many of the posts but I have not found the solution that I am looking for. I am familiar with VB and the NOW function.
I am trying to create a macro that will reference a cell and place a static date and time next to the cell. I want this date and time to only change when the checkbox is clicked.
I have a checkbox in E11 that is linked to F11, I would like to have G11 record the date and time that the checkbox is clicked. I have already tried the following.

Please Login or Register  to view this content.

I would appreciate any help.

**squiggler47** · 07-01-2009, 02:16 PM

how about one with no macro?

**JBeaucaire** · 07-01-2009, 02:34 PM

I think you're working too hard. Put the code in the checkbox itself.

Right-click on the checkbox and select VIEW CODE. Create a macro that looks like this:

Please Login or Register  to view this content.

You don't even need to link it to a cell.

**nsorden** · 07-01-2009, 02:45 PM

I have many checkboxes...putting the code in each checkbox is not feasible.

Also, the above solution suggests a formula that requires changing to iterative calculations. With the workbook shared and multiple formulas running I would not want to use that sort of calculation right?

**JBeaucaire** · 07-01-2009, 02:51 PM

I don't use circular references, but they have their place. Up to you on whether they're acceptable or not.

Going with your original approach, also feasible, it appears you want a timestamp in column G anytime a value in column F changes (caused by your checkboxes, yes?)...:

Please Login or Register  to view this content.

Adjust the red range to however much of column F you want to watch.

**nsorden** · 07-01-2009, 03:07 PM

Please Login or Register  to view this content.

Causes an error-Range of Object worksheet failed.
It only does this when something is typed into the F column.
It does not change when the checkbox is clicked.

**JBeaucaire** · 07-01-2009, 03:09 PM

Your syntax then:

Please Login or Register  to view this content.

**nsorden** · 07-01-2009, 03:23 PM

Yes, that works fine if I type something into cell F10-> G10 records the date&time
But, if the check box is linked to F10 and F10 changes from True to False...nothing happens.

**JBeaucaire** · 07-01-2009, 04:18 PM

Ah, didn't see that coming.

Well, I did say not to do it that way. If the only reason you don't want to program the checkboxes is because there's so many of them, that's been faced before, too.

Here's a link to a thread where an entire macro solution is offered to delete your existing checkboxes, put in new ones with their own macro. The new macro appears to effectively do the same thing your "linked" cells do, except the new replacement checkboxes do it in a way that triggers the worksheet_change macros.

Have a look: (Dave Peterson's post)
http://www.microsoft.com/communities...&cr=&sloc=&p=1

**nsorden** · 07-02-2009, 09:27 AM

I keep running into an error in this part of the code:

Please Login or Register  to view this content.

**nsorden** · 07-02-2009, 10:45 AM

Is there no way to just make a linked cell=Now and have that cell not change everytime the sheet recalculates? Why does this seem so impossible?

This worksheet change event works when a cell is changed:

Please Login or Register  to view this content.

Isn't there some way to build this into the control options of the Checkbox click? Or assign a macro to make the now function carry over?

If I were to just assign a macro to each checkbox how might I make the macro reference only the checkbox that was clicked(ie the one that triggers the macro)?

**JBeaucaire** · 07-02-2009, 11:32 AM

Originally Posted by nsorden

Is there no way to just make a linked cell=Now and have that cell not change everytime the sheet recalculates? Why does this seem so impossible?

1) It's not impossible, I showed you a way to do that in post #3. You said "no".

2) I showed you another way to do it just having changes in column F trigger a timestamp in post #7. You're need to use checkboxes without option 1) above means you've said "no" again.

It's impossible because you discount working solutions.

For me:
I wouldn't use a "checkbox" to put a hidden value in a cell in an attempt to trigger a date in another column. I would put an "x" in column F and let the timestamp appear and move on.

100s of checkboxes is a design nightmare...something I believe you've discovered for yourself.

**nsorden** · 07-02-2009, 12:10 PM

1) It's not impossible, I showed you a way to do that in post #3. You said "no".

Post #3 provided for only 1 checkbox and would not work for many check boxes

2) I showed you another way to do it just having changes in column F trigger a timestamp in post #7. You're need to use checkboxes without option 1) above means you've said "no" again.

The worksheet change event places a timestamp in the column when the cell is clicked. I have formulas that depend on that date and time. This means that if the cell is accidentally clicked by a user the date and time will populate, generating false results for other functions. This also did not work with the checkboxes-my original question.

It's impossible because you discount working solutions.

I am not discounting working solutions…I am just not getting the solution. I have been clear in my posts and have been willing to try all of the solutions.
I have asked questions on how to solve the problems-but I have been pointed back to the same idea-post#3 each time-an idea which has not worked.

For me:
I wouldn't use a "checkbox" to put a hidden value in a cell in an attempt to trigger a date in another column. I would put an "x" in column F and let the timestamp appear and move on.

As far as placing an X in F, as I stated above-that will return a value if clicked. I am trying to have the cell populate with a timestamp only when clicked. Placing an X, then deleting that X still results in a timestamp with the above solution.

I have found the following code that makes 100s of checkboxes easy.

Please Login or Register  to view this content.

However, I still need a macro that will refer to the variable checkbox and run the macro-placing the timestamp in a variable cell location. This is what I have so farbut it returns an error "Compile error invalid or unqualified reference" at line 3.

Please Login or Register  to view this content.

**JBeaucaire** · 07-02-2009, 12:48 PM

I don't know a way to resolve all the issues the way you want. I would step back and look for an alternate route.

For instance, we know the checkboxes can do their TRUE/FALSE thing in column F, no problem. And you want an unchanging timestamp in column G.

So, what about formulas in column G like this, from G2 down:
=IF(F2=TRUE,NOW(),"")

Then, use a workbook_beforesave macro to scan column G and replace all values greater the "" with their own value. In the ThisWorkbook module:

Please Login or Register  to view this content.

Now, you don't have to fiddle with checkbox code at all.

**nsorden** · 07-06-2009, 04:34 PM

These two links helped to solve my problem...
http://www.excelforum.com/excel-prog...eck-boxes.html

http://www.excelforum.com/excel-prog...and-dates.html

Record Date and Time Macro

LinkBack

Thread Tools

Rate This Thread

Display

Record Date and Time Macro

Re: Record Date and Time Macro

Re: Record Date and Time Macro

Re: Record Date and Time Macro

Re: Record Date and Time Macro

Re: Record Date and Time Macro

Re: Record Date and Time Macro

Re: Record Date and Time Macro

Re: Record Date and Time Macro

Re: Record Date and Time Macro

Re: Record Date and Time Macro

Re: Record Date and Time Macro

Re: Record Date and Time Macro

Re: Record Date and Time Macro

Re: Record Date and Time Macro

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions