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

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


"Hendrik.Kleine" wrote:

> 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

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


"Hendrik.Kleine" wrote:

> 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