Timestamp and Macros Question

ziemanc

New Member
Joined
Jun 14, 2012
Messages
2
hello,

Long story and complicated workbook down to the issue at heart... I have a worksheet where those submitting work to me can simply open the file, place an X in a table with respect to what they have completed/submitted, and excel creates a TIMESTAMP() based on this code:

Public Function TIMESTAMP() As Date
TIMESTAMP = Now
End Function

I did this in order to overcome the automatically updating NOW() function. However, when you exit out of the file, and reenter, then Enable Macros, it just gives the current time instead of the time the X was originally placed.

Anyone have any ideas on how to overcome this. I was thinking maybe something to paste special value, but then it would still be referencing the cell that the date was placed which would change when opening the file. Can you paste special value only once and then NOT update it after that?

I believe one solution would be to make it a trusted location but that would be very difficult considering the company security and the fact that people will be accessing this document and amending it all over the world.

I'm in intern and was given an assignment of tracking and calculating statistics on late/early submissions for our profit centers worldwide. I would love to figure out how to solve this issue, could be something great toward a future job offering.

Thanks!!

Chase
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

nise23

Board Regular
Joined
Jan 6, 2012
Messages
65
I have had a similar situation before and solved it with the following formula to create the date stamp:

=IF(A1<>"",IF(B1="",NOW(),B1),"")

...where A1 would be the cell where the X is placed and B1 the cell where the time stamp is placed. It basically does not recalculate if a time stamp is already in the cell.

The formula creates a circular reference error so iterative calculations have to be turned on either in the Excel Options or through code (through code is better if there are many different people using the workbook):

Code:
Application.Iteration = True

Let me know if this works for you!

-nise23
 
Last edited:

ziemanc

New Member
Joined
Jun 14, 2012
Messages
2
it looks like it would work. i have been googling for the code to turn on iterative calculations and cant find anything. do you know what it would be?

Thanks!!
 

nise23

Board Regular
Joined
Jan 6, 2012
Messages
65
I found this on a different forum (http://www.excelforum.com/excel-programming/470868-calculation-iteration.html)

Code:
Private Sub Workbook_Open()
Application.Iteration = True
Application.MaxIterations = xx
Application.MaxChange = yy
End Sub

But I don't think it is necessary to limit maximum iterations or maximum change, so this should suffice:

Code:
Private Sub Workbook_Open()
Application.Iteration = True
End Sub

This should be added to the Workbook module and will run upon opening of the workbook.

Hope this works :)

-nise23
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,989
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top