Date and Timestamp at worksheet level

[ashlad]

I have a workbook which is being used by a number of users. Each worksheet is owned by a user and managed by a administrator. When a change is made to a worksheet, I need a timestamp for date and time on saving. This has to be at the worksheet level and each worksheet must have its own individual timestamp (not an overall workbook save timestamp).

Hope you can help.

TIA

Ashlad

 

[phantom1975]

WELCOME TO THE BOARD!

One way you can do this is with a Worksheet_Change Macro like this:

Range("A1").Value = Now()

This will put the time/date stamp in cell A1.

Is this what you were looking for?

 

[Ivan F Moala]

On 2002-08-23 00:34, ashlad wrote:
I have a workbook which is being used by a number of users. Each worksheet is owned by a user and managed by a administrator. When a change is made to a worksheet, I need a timestamp for date and time on saving. This has to be at the worksheet level and each worksheet must have its own individual timestamp (not an overall workbook save timestamp).

Hope you can help.

TIA

Ashlad

Hi Ashlad
Asyou are need to change ALL worksheets then
it is better to use the Workbooks Sheet change event rather then Code for each sheet in the Worksheet_Change event of the Sheet.

eg.
<pre/>
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Sh.[A1] = Now
End Sub
</pre>

 

[ashlad]

Thanks for the welcome

I need to set the time by sheet and not workbook.

I am not very good with VB, so is the code you specified a formula or do I need to create a piece of vb??

Cheers

Ashlad

 

[Paul B]

Use phantom1975 code and put it in the worksheet code, right click on the sheet tab, view code and paste the code there

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Range("A1").Value = Now()
End Sub

 

[ashlad]

that worked thanks a lot.

the problem with it is that everytime you have a change the routine runs twice because the timestamp is also counted. hence you get a small delay as you leave one cell to go to the next.

To solve this i have put a if statement round the command. The code looks like

Private Sub Worksheet_Change(ByVal Target As Range)
If Target <> Cells(5, 4) Then
Cells(5, 4) = Format(Now, "dd mmmm yyyy, hh:mm:ss")
End If
End Sub

the problem I am now having with this, is that you can not take a cell in the sheet and copy across a number of cells as the if statement returns the error "run type error (13) type mismatch"

Any ideas???

Thanks
Ashlad