That line looks perfect to me, but it didn't work for me, too.

But I could use:

i = .Range("a65536").End(xlUp).Row

If you're using xl2007, you can make it
i = .Range("a1048576").End(xlUp).Row

Or whatever that huge number of rows is.

(It looks like a bug in excel (not your code) to me.)

bridgesmj wrote:

Thanks once again people, there are some good ideas here. I especially
like the idea of logging when people save, since as this actually what
I want to know. Also, if they were that switched on they might wonder
why they were being asked to save the workbook again when they've
already just saved it (i.e. after the macro makes the changes to the
userlog worksheet).

I've made some modifcations to this code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim i As Long
With Worksheets("hiiden sheet")
i = .Cells(.Rows.Count, "A").End(xlUp).Row
If i = 1 And .Range("A1").Value = "" Then
Else
i = i + 1
End If
.Range("A" & i).Value = Environ("UserName")
.Range("B" & i).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End With
End Sub

to basically reflect the fact that I use a "userlog" worksheet. I've
also created a macro in my personal workbook that allows me to toggle
userlog between visible and non visible.

After a bit of testing though, I came to realise that the above code
has a bug in line 4 (i = .Cells(.Rows.Count, "A").End(xlUp).Row) when
the workbook is closed with an active chart (not active sheet).

This obviously causes me problems, as I'm trying to log user
information covertly, and a nasty dialog asking the user to debug code
is not conducive to this.

Ultimately I'll be wanting to save this information to an external
workbook that is rights protected.

I look forward to reading your thoughts. I'll definitely be
implementing a BeforeSave script.

Thanks again in advance,

Mark.

Gord Dibben wrote:
Maybe set the name and date/time when the user saves the workbook.

Environ("UserName") is the logon name.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI _
As Boolean, Cancel As Boolean)
With ThisWorkbook
With ThisWorkbook
Sheets("Sheet1").Visible = xlVeryHidden
With Worksheets("Sheet1")
.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0).Value = "Last Saved By " _
& Environ("UserName") & " " & Now
End With
End With
End Sub

Or automatically save the workbook when user hits Close

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With ThisWorkbook
Sheets("Sheet1").Visible = xlVeryHidden
With Worksheets("Sheet1")
.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0).Value = "Last Saved By " _
& Environ("UserName") & " " & Now
End With
.Save
End With
End Sub

Whichever of these you choose would be entered into the Thisworkbook module.

Right-click on the Excel logo left of "File" on menu bar. and "View Code"

Paste into that module.

For you to see Sheet1 enter this in the Immediate Window

Sheets("Sheet1").Visible = True


Gord Dibben MS Excel MVP

On 16 Jan 2007 09:39:38 -0800, "bridgesmj" wrote:

Hi, I've been thinking how to do this, but I'm not quite advanced
enough to crack it yet.

I'd like to create a macro that saves the user identity (preferrably
system logon ID, but User name from Options would suffice), and date
and time of closure to a hidden sheet in each workbook every time a
workbook is closed.

I'm having trouble with people mucking up my work and I want to know
who it is! A few people need access to edit them, so there's no
blocking them off - sorry if that was your other solution.

Thanks in advance for this.

Mark.

--

Dave Peterson