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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> 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" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)>
>> > 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)>
>> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)>
> >> > 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