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.

On the ThisWorkbook tab place this, also look up useraccesslist, it allows
you to set protection based on userid, might be better in the future.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
lastcell = Sheets("sheet3").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("sheet3").Cells(lastcell + 1, 1) = Application.UserName & ", " &
Now()
End Sub


--
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"bridgesmj" wrote in message
ups.com...
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.

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


'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"bridgesmj" wrote in message
ups.com...
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.

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.

Great, that works exactly as I wanted it to. Thanks very much.

Bob Phillips wrote:

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


'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"bridgesmj" wrote in message
ups.com...
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.

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.

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

You might want to change it to spell hidden correctly as well <G

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Dave Peterson" wrote in message
...
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

With regard to be asked to save again, you could save it yourself

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim i As Long
With Worksheets("hidden sheet")
i = .Range("a65536").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
ThisWorkbook.Save
End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"bridgesmj" wrote in message
ups.com...
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.

It's a top secret way of keeping that worksheet hiiden, er, hidden.

Bob Phillips wrote:

You might want to change it to spell hidden correctly as well <G

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

"Dave Peterson" wrote in message
...
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

--

Dave Peterson

Thanks people. Putting different bits of people's code together I've
done what I wanted.

I won't be putting any Save routines in - the last thing I want to do
is save any changes that a user might make if they weren't intending to
save atall (i.e. they might save whilst working with the workbook, but
not intend to save just before they close it. I know this contradicts
my original goal, but that kind of changed!) The auto command should
come with a health warning!

Cheers,

Mark

Dave Peterson wrote:
It's a top secret way of keeping that worksheet hiiden, er, hidden.

Bob Phillips wrote:

You might want to change it to spell hidden correctly as well <G

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

"Dave Peterson" wrote in message
...
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

--

Dave Peterson