Hi,
I'm using the following macro in the ThisWorkbook module which creates a log
when somebody opens a file:

Private Sub Workbook_Open()
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now, ThisWorkbook.FullName
Close #1
End Sub

It works perfectly, but I only have it in a template. My question
therefore, is can the macro be adapted so that if someone opens the template,
does File - Save As and renames it, this new file is also listed on the log
without having to re-open it?

Hope this makes sense!

Try this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim sFilename As String
If SaveAsUI Then
Cancel = True
sFilename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel Files (*.xls), *.xls")
If sFilename < "False" Then
ThisWorkbook.SaveAs sFilename
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now, ThisWorkbook.FullName
Close #1
End If
End If
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PaulJ" wrote in message
...
Hi,
I'm using the following macro in the ThisWorkbook module which creates a
log
when somebody opens a file:

Private Sub Workbook_Open()
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now, ThisWorkbook.FullName
Close #1
End Sub

It works perfectly, but I only have it in a template. My question
therefore, is can the macro be adapted so that if someone opens the
template,
does File - Save As and renames it, this new file is also listed on the
log
without having to re-open it?

Hope this makes sense!

Thanks Bob, but it's not working as I would like just yet. I don't know much
VBA so the syntax below doesn't mean a great deal to me, but maybe I should
explain further...

If the code was in a file named Template, then on opening this file a log is
created containing my username, time and filename. If I then saved this
Template as Template2 I require the details of this file to also appear on
the same log as soon as it is created.

Thanks :-)
"Bob Phillips" wrote:

Try this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim sFilename As String
If SaveAsUI Then
Cancel = True
sFilename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel Files (*.xls), *.xls")
If sFilename < "False" Then
ThisWorkbook.SaveAs sFilename
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now, ThisWorkbook.FullName
Close #1
End If
End If
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PaulJ" wrote in message
...
Hi,
I'm using the following macro in the ThisWorkbook module which creates a
log
when somebody opens a file:

Private Sub Workbook_Open()
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now, ThisWorkbook.FullName
Close #1
End Sub

It works perfectly, but I only have it in a template. My question
therefore, is can the macro be adapted so that if someone opens the
template,
does File - Save As and renames it, this new file is also listed on the
log
without having to re-open it?

Hope this makes sense!

So what is happening in your tests?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PaulJ" wrote in message
...
Thanks Bob, but it's not working as I would like just yet. I don't know
much
VBA so the syntax below doesn't mean a great deal to me, but maybe I
should
explain further...

If the code was in a file named Template, then on opening this file a log
is
created containing my username, time and filename. If I then saved this
Template as Template2 I require the details of this file to also appear on
the same log as soon as it is created.

Thanks :-)
"Bob Phillips" wrote:

Try this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim sFilename As String
If SaveAsUI Then
Cancel = True
sFilename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel Files (*.xls), *.xls")
If sFilename < "False" Then
ThisWorkbook.SaveAs sFilename
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now, ThisWorkbook.FullName
Close #1
End If
End If
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PaulJ" wrote in message
...
Hi,
I'm using the following macro in the ThisWorkbook module which creates
a
log
when somebody opens a file:

Private Sub Workbook_Open()
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now, ThisWorkbook.FullName
Close #1
End Sub

It works perfectly, but I only have it in a template. My question
therefore, is can the macro be adapted so that if someone opens the
template,
does File - Save As and renames it, this new file is also listed on
the
log
without having to re-open it?

Hope this makes sense!

Nothing is going into the log at all right now. Maybe the problem is the
Private Sub title, which is showing up in red text? When I open the file I'm
not getting the macro security warning, so I guess the macro is not being
recognised?

"Bob Phillips" wrote:

So what is happening in your tests?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PaulJ" wrote in message
...
Thanks Bob, but it's not working as I would like just yet. I don't know
much
VBA so the syntax below doesn't mean a great deal to me, but maybe I
should
explain further...

If the code was in a file named Template, then on opening this file a log
is
created containing my username, time and filename. If I then saved this
Template as Template2 I require the details of this file to also appear on
the same log as soon as it is created.

Thanks :-)
"Bob Phillips" wrote:

Try this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim sFilename As String
If SaveAsUI Then
Cancel = True
sFilename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel Files (*.xls), *.xls")
If sFilename < "False" Then
ThisWorkbook.SaveAs sFilename
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now, ThisWorkbook.FullName
Close #1
End If
End If
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PaulJ" wrote in message
...
Hi,
I'm using the following macro in the ThisWorkbook module which creates
a
log
when somebody opens a file:

Private Sub Workbook_Open()
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now, ThisWorkbook.FullName
Close #1
End Sub

It works perfectly, but I only have it in a template. My question
therefore, is can the macro be adapted so that if someone opens the
template,
does File - Save As and renames it, this new file is also listed on
the
log
without having to re-open it?

Hope this makes sense!

Paul,

I think it is just NG wrap-around problems. It certainly works, I tested it
(after you replied <vbg).

Try this update

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim sFilename As String
If SaveAsUI Then
Cancel = True
sFilename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel Files (*.xls), *.xls")
If sFilename < "False" Then
ThisWorkbook.SaveAs sFilename
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now, ThisWorkbook.FullName
Close #1
End If
End If
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PaulJ" wrote in message
...
Nothing is going into the log at all right now. Maybe the problem is the
Private Sub title, which is showing up in red text? When I open the file
I'm
not getting the macro security warning, so I guess the macro is not being
recognised?

"Bob Phillips" wrote:

So what is happening in your tests?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PaulJ" wrote in message
...
Thanks Bob, but it's not working as I would like just yet. I don't
know
much
VBA so the syntax below doesn't mean a great deal to me, but maybe I
should
explain further...

If the code was in a file named Template, then on opening this file a
log
is
created containing my username, time and filename. If I then saved
this
Template as Template2 I require the details of this file to also
appear on
the same log as soon as it is created.

Thanks :-)
"Bob Phillips" wrote:

Try this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim sFilename As String
If SaveAsUI Then
Cancel = True
sFilename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel Files (*.xls), *.xls")
If sFilename < "False" Then
ThisWorkbook.SaveAs sFilename
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now,
ThisWorkbook.FullName
Close #1
End If
End If
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PaulJ" wrote in message
...
Hi,
I'm using the following macro in the ThisWorkbook module which
creates
a
log
when somebody opens a file:

Private Sub Workbook_Open()
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now, ThisWorkbook.FullName
Close #1
End Sub

It works perfectly, but I only have it in a template. My question
therefore, is can the macro be adapted so that if someone opens
the
template,
does File - Save As and renames it, this new file is also listed
on
the
log
without having to re-open it?

Hope this makes sense!

Thanks Bob that certainly seems to have done the trick. Just one final thing
though! The saved file is appearing in the log but not the original
template...is there a way to have both?

Thanks for your patience!
Paul

"Bob Phillips" wrote:

Paul,

I think it is just NG wrap-around problems. It certainly works, I tested it
(after you replied <vbg).

Try this update

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim sFilename As String
If SaveAsUI Then
Cancel = True
sFilename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel Files (*.xls), *.xls")
If sFilename < "False" Then
ThisWorkbook.SaveAs sFilename
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now, ThisWorkbook.FullName
Close #1
End If
End If
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PaulJ" wrote in message
...
Nothing is going into the log at all right now. Maybe the problem is the
Private Sub title, which is showing up in red text? When I open the file
I'm
not getting the macro security warning, so I guess the macro is not being
recognised?

"Bob Phillips" wrote:

So what is happening in your tests?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PaulJ" wrote in message
...
Thanks Bob, but it's not working as I would like just yet. I don't
know
much
VBA so the syntax below doesn't mean a great deal to me, but maybe I
should
explain further...

If the code was in a file named Template, then on opening this file a
log
is
created containing my username, time and filename. If I then saved
this
Template as Template2 I require the details of this file to also
appear on
the same log as soon as it is created.

Thanks :-)
"Bob Phillips" wrote:

Try this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim sFilename As String
If SaveAsUI Then
Cancel = True
sFilename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel Files (*.xls), *.xls")
If sFilename < "False" Then
ThisWorkbook.SaveAs sFilename
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now,
ThisWorkbook.FullName
Close #1
End If
End If
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PaulJ" wrote in message
...
Hi,
I'm using the following macro in the ThisWorkbook module which
creates
a
log
when somebody opens a file:

Private Sub Workbook_Open()
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now, ThisWorkbook.FullName
Close #1
End Sub

It works perfectly, but I only have it in a template. My question
therefore, is can the macro be adapted so that if someone opens
the
template,
does File - Save As and renames it, this new file is also listed
on
the
log
without having to re-open it?

Hope this makes sense!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim sFilename As String
If SaveAsUI Then
Cancel = True
sFilename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel Files (*.xls), *.xls")
If sFilename < "False" Then
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now, ThisWorkbook.FullName
ThisWorkbook.SaveAs sFilename
Print #1, Environ("username"), Now, ThisWorkbook.FullName
Close #1
End If
End If
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" wrote in message
...
Paul,

I think it is just NG wrap-around problems. It certainly works, I tested
it
(after you replied <vbg).

Try this update

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim sFilename As String
If SaveAsUI Then
Cancel = True
sFilename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel Files (*.xls), *.xls")
If sFilename < "False" Then
ThisWorkbook.SaveAs sFilename
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now, ThisWorkbook.FullName
Close #1
End If
End If
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PaulJ" wrote in message
...
Nothing is going into the log at all right now. Maybe the problem is
the
Private Sub title, which is showing up in red text? When I open the
file
I'm
not getting the macro security warning, so I guess the macro is not
being
recognised?

"Bob Phillips" wrote:

So what is happening in your tests?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PaulJ" wrote in message
...
Thanks Bob, but it's not working as I would like just yet. I don't
know
much
VBA so the syntax below doesn't mean a great deal to me, but maybe I
should
explain further...

If the code was in a file named Template, then on opening this file
a
log
is
created containing my username, time and filename. If I then saved
this
Template as Template2 I require the details of this file to also
appear on
the same log as soon as it is created.

Thanks :-)
"Bob Phillips" wrote:

Try this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel
As
Boolean)
Dim sFilename As String
If SaveAsUI Then
Cancel = True
sFilename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel Files (*.xls), *.xls")
If sFilename < "False" Then
ThisWorkbook.SaveAs sFilename
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now,
ThisWorkbook.FullName
Close #1
End If
End If
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PaulJ" wrote in message
...
Hi,
I'm using the following macro in the ThisWorkbook module which
creates
a
log
when somebody opens a file:

Private Sub Workbook_Open()
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now, ThisWorkbook.FullName
Close #1
End Sub

It works perfectly, but I only have it in a template. My
question
therefore, is can the macro be adapted so that if someone opens
the
template,
does File - Save As and renames it, this new file is also listed
on
the
log
without having to re-open it?

Hope this makes sense!

Fantastic! Thanks very much Bob :)

"Bob Phillips" wrote:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim sFilename As String
If SaveAsUI Then
Cancel = True
sFilename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel Files (*.xls), *.xls")
If sFilename < "False" Then
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now, ThisWorkbook.FullName
ThisWorkbook.SaveAs sFilename
Print #1, Environ("username"), Now, ThisWorkbook.FullName
Close #1
End If
End If
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" wrote in message
...
Paul,

I think it is just NG wrap-around problems. It certainly works, I tested
it
(after you replied <vbg).

Try this update

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim sFilename As String
If SaveAsUI Then
Cancel = True
sFilename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel Files (*.xls), *.xls")
If sFilename < "False" Then
ThisWorkbook.SaveAs sFilename
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now, ThisWorkbook.FullName
Close #1
End If
End If
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PaulJ" wrote in message
...
Nothing is going into the log at all right now. Maybe the problem is
the
Private Sub title, which is showing up in red text? When I open the
file
I'm
not getting the macro security warning, so I guess the macro is not
being
recognised?

"Bob Phillips" wrote:

So what is happening in your tests?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PaulJ" wrote in message
...
Thanks Bob, but it's not working as I would like just yet. I don't
know
much
VBA so the syntax below doesn't mean a great deal to me, but maybe I
should
explain further...

If the code was in a file named Template, then on opening this file
a
log
is
created containing my username, time and filename. If I then saved
this
Template as Template2 I require the details of this file to also
appear on
the same log as soon as it is created.

Thanks :-)
"Bob Phillips" wrote:

Try this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel
As
Boolean)
Dim sFilename As String
If SaveAsUI Then
Cancel = True
sFilename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel Files (*.xls), *.xls")
If sFilename < "False" Then
ThisWorkbook.SaveAs sFilename
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now,
ThisWorkbook.FullName
Close #1
End If
End If
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PaulJ" wrote in message
...
Hi,
I'm using the following macro in the ThisWorkbook module which
creates
a
log
when somebody opens a file:

Private Sub Workbook_Open()
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now, ThisWorkbook.FullName
Close #1
End Sub

It works perfectly, but I only have it in a template. My
question
therefore, is can the macro be adapted so that if someone opens
the
template,
does File - Save As and renames it, this new file is also listed
on
the
log
without having to re-open it?

Hope this makes sense!