Hi,

I would like to have automatic date or time stamp in specific cell when user firstly edit workbook or sheet using excel template file. All found functions using workbook open or change, but I want to achieve not changing date after first edit of the workbook. Any help?

Hi,

I would like to have automatic date or time stamp in specific cell when user
firstly edit workbook or sheet using excel template file. All found functions
using workbook open or change, but I want to achieve not changing date after
first edit of the workbook. Any help?

Leave the cell empty and test for that when opened.

To test if any sheet in the file gets edited, you can place the date/time into
the cell using the Now() function via the Workbook_SheetChange event.

Use the Worksheet_Change event for testing if individual sheets get edited. You
could do this in the Workbook_SheetChange event as well.

You could also add the username of the person who did the editing; -so 1st edit
gets the date/time stamp + delimiter + username and subsequent edits get
delimiter + username appended to the existing 'Value'. You could also add the
date/time stamp to subsequent edits using a different delimiter so your code
knows which to use when!

I suggest using a named cell local to the sheet where the cell resides; -where
the cell is hidden (row above the data in a fixed column works best). This will
require referncing the sheet named range when reading/writing the cell, so
giving it local scope allows reusing the same name on sheets without conflict
since workbook events include a ref to the sheet. Worksheet events are ref'd
using the keyword 'Me'.

So...

In a standard module Declarations section:

Option Explicit

Public gwksProgData As Worksheet, grngEdit As Range
Public gsUser$, gbEdited As Boolean

Public Const gsRngEdit$ = "ptrEdit" '//local scope named range
Public Const gsDelimited$ = "|" '//list delimiter
Public Const gsDelimiter$ = ":" '//value pair delimiter


Sub InitGlobals()
' Initializes global variables at startup
Set gwksProgData = ThisWorkbook.Sheets("ProgData") '//stores project data
gsUser = Environ("username") '//current user
'initialize others as required
End Sub

Sub ShutdownCleanup()
' Releases runtime objects from memory
On Error Resume Next
Set gwksProgData = Nothing: Set grngEdit = Nothing
End Sub

Sub RecordEdit()
' Stores when/who edits this file
' Supports listing delimited value pairs
If Not gbEdited Then Exit Sub '//no edits

With grngEdit
If .Value = Empty Then
.Value = Now() '& gsDelimiter & gsUser
Else '//it's being edited again
' .Value = .Value & gsDelimited & Now() & gsDelimiter & gsUser
End If
End With
gbEdited = False '//reset flag
End Sub 'RecordEdit


In the ThisWorkbook code:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call ShutdownCleanup
End Sub

Private Sub Workbook_Open()
Call InitGlobals
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
gbEdited = True
Set grngEdit = gwksProgData.Range(gsRngEdit): RecordEdit
End Sub


If also using this for tracking individual worksheets, in the worksheet code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
gbEdited = True
Set grngEdit = Me.Range(gsRngEdit): RecordEdit
End Sub

...where project data/settings/lists etc. are stored on a hidden sheet named
"ProgData" and accessed by any code in the workbook. Individual sheets store in
hidden ranges or use local scope Defined Names.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion