Calculate cell once (on workbook open)

[Guest]

I have a formula that results in the date/time (NOW) a user (network name)
opens the file.

Problem is, any time a change is made this formula is updated with the most
current time. Is there any way that I can make this particular cell calculate
only on workbook open. In addidtion, can I make it calculate only if a
certain user opens the file? (user1 opens the file, cell A1 is caculated once
on opening, user2 opens the file, cell A2 is calculated once on opening but
cell A1 remains unchanged).

Thanks in advance for any feedback on this (if it's possible at all)

Hendrik

 

[Guest]

Try this:-

Private Sub Workbook_Open()
usernamewindows = Environ("USERNAME")
Select Case usernamewindows
Case Is = "John"
myrange = ("A1")
Case Is = "dave"
myrange = ("A2")
Case Is = "Pete"
myrange = ("A3")
Case Else
myrange = ("A4")
End Select
Sheets("Sheet1").Range(myrange) = Date
End Sub

Mike

 

[Guest]

Hi Hendrik

The formula now() is calculated anytime the sheet is calculated. You should
use an Auto_Open macro. Depending on the user opening the file you should
write:
User 1: range("A1")=now(), User 2: range("A2")=now()
This way the cells contain the actual value at starting the file and no more
a formula.

Regards
reklamo