Last user to make a change
 

I have the following code at the workbook level of a spreadsheet

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Range("LastChange").Value = Format(Now(), "dd mmm yyyy")
Range("LastUser").Value = Environ("username")
End Sub

I have the named ranges. Why doesn't this work?

LWHite

Last user to make a change
 

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
With ThisWorkbook.Names
.Item("LastChange").RefersTo = "=""" & _
Format(Now(), "dd mmm yyyy") & """"
.Item("LastUser").RefersTo = Environ("username")
End With
End Sub

--
Regards,
Tom Ogilvy

"L.White" wrote in message
...
I have the following code at the workbook level of a spreadsheet

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Range("LastChange").Value = Format(Now(), "dd mmm yyyy")
Range("LastUser").Value = Environ("username")
End Sub

I have the named ranges. Why doesn't this work?

LWHite

Last user to make a change
 

At the point in the code which says

& """".Item

I am getting an error message that says expected end of statement. should I
remove the . or perhaps add () around the """"

Also, I received a error warning about the _ at the point of
="="" &_ Format
I removed the _ and it took that portion of the code. But saving the sheet
caused no changes. I then tried to get things to work by changing them
around like so

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
With ThisWorkbook.Names.Item("LastChange").RefersTo = "=""" & Format(Now(),
"dd mmm yyyy")
End With
With ThisWorkbook.Names.Item("LastUser").RefersTo = Environ("username")
End With
End Sub

No errors but no results.

Thanks Tom, I appreciate your help.
LWhite

"Tom Ogilvy" wrote in message
...
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
With ThisWorkbook.Names
.Item("LastChange").RefersTo = "=""" & _
Format(Now(), "dd mmm yyyy") & """"
.Item("LastUser").RefersTo = Environ("username")
End With
End Sub

--
Regards,
Tom Ogilvy

"L.White" wrote in message
...
I have the following code at the workbook level of a spreadsheet

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Range("LastChange").Value = Format(Now(), "dd mmm yyyy")
Range("LastUser").Value = Environ("username")
End Sub

I have the named ranges. Why doesn't this work?

LWHite

Last user to make a change
 

the code was copied from the the workbook module of a workbook where it was
working very well. Certainly no compile errors, so the only conclusion
would be that the code you have is not identical to the code posted.
Google has been known to put in stray hyphens in the code (if you copy it
from the posting) and it is possible wordwrap in the email may have caused
problems. I didn't post any code that has :

& """".Item

nor did I have a line like

="="" &_ Format

that line was broken on two separate lines (for a reason).

it looks like the code has somehow been mangled in transmission.

here it is again:


Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, Cancel As _
Boolean)
With ThisWorkbook.Names
.Item("LastChange").RefersTo = "=""" & _
Format(Now(), "dd mmm yyyy") & """"
.Item("LastUser").RefersTo = Environ("username")
End With
End Sub

It works fine as written, so I don't know what to tell you.

If it still looks mangled, then I can send you a sample workbook directly.

--
Regards,
Tom Ogilvy





"L.White" wrote in message
...
At the point in the code which says

& """".Item

I am getting an error message that says expected end of statement. should
I
remove the . or perhaps add () around the """"

Also, I received a error warning about the _ at the point of
="="" &_ Format
I removed the _ and it took that portion of the code. But saving the
sheet
caused no changes. I then tried to get things to work by changing them
around like so

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
With ThisWorkbook.Names.Item("LastChange").RefersTo = "=""" &
Format(Now(),
"dd mmm yyyy")
End With
With ThisWorkbook.Names.Item("LastUser").RefersTo = Environ("username")
End With
End Sub

No errors but no results.

Thanks Tom, I appreciate your help.
LWhite

"Tom Ogilvy" wrote in message
...
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
With ThisWorkbook.Names
.Item("LastChange").RefersTo = "=""" & _
Format(Now(), "dd mmm yyyy") & """"
.Item("LastUser").RefersTo = Environ("username")
End With
End Sub

--
Regards,
Tom Ogilvy

"L.White" wrote in message
...
I have the following code at the workbook level of a spreadsheet

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Range("LastChange").Value = Format(Now(), "dd mmm yyyy")
Range("LastUser").Value = Environ("username")
End Sub

I have the named ranges. Why doesn't this work?

LWHite

Last user to make a change
 

I don't know if it is getting mangeled or not. I see the following:


here it is again:


Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, Cancel As _
Boolean)
With ThisWorkbook.Names
.Item("LastChange").RefersTo = "=""" & _
Format(Now(), "dd mmm yyyy") & """"
.Item("LastUser").RefersTo = Environ("username")
End With
End Sub


If that is different than the way you submitted it to Google then would you
please go ahead and forward it to me directly. Again, thank you very much
for your help. I really do appreciate it.

LWhite

"Tom Ogilvy" wrote in message
...
the code was copied from the the workbook module of a workbook where it
was
working very well. Certainly no compile errors, so the only conclusion
would be that the code you have is not identical to the code posted.
Google has been known to put in stray hyphens in the code (if you copy it
from the posting) and it is possible wordwrap in the email may have caused
problems. I didn't post any code that has :

& """".Item

nor did I have a line like

="="" &_ Format

that line was broken on two separate lines (for a reason).

it looks like the code has somehow been mangled in transmission.

here it is again:


Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, Cancel As _
Boolean)
With ThisWorkbook.Names
.Item("LastChange").RefersTo = "=""" & _
Format(Now(), "dd mmm yyyy") & """"
.Item("LastUser").RefersTo = Environ("username")
End With
End Sub

It works fine as written, so I don't know what to tell you.

If it still looks mangled, then I can send you a sample workbook directly.

--
Regards,
Tom Ogilvy

Last user to make a change
 

I created a new workbook. Saved it. I create the two names. then I copied
the code from you email and pasted into the Thisworkbook module of the
workbook and Saved the workbook.

from the immediate window:

? activeworkbook.Names(1).Name
LastChange
? activeworkbook.Names(1).RefersTo
="26 Aug 2005"
? activeworkbook.Names(2).Name
LastUser
? activeworkbook.Names(2).RefersTo
="ogilvytw"


--
Regards,
Tom Ogilvy

"L.White" wrote in message
...
I don't know if it is getting mangeled or not. I see the following:


here it is again:


Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, Cancel As _
Boolean)
With ThisWorkbook.Names
.Item("LastChange").RefersTo = "=""" & _
Format(Now(), "dd mmm yyyy") & """"
.Item("LastUser").RefersTo = Environ("username")
End With
End Sub


If that is different than the way you submitted it to Google then would
you
please go ahead and forward it to me directly. Again, thank you very much
for your help. I really do appreciate it.

LWhite

"Tom Ogilvy" wrote in message
...
the code was copied from the the workbook module of a workbook where it
was
working very well. Certainly no compile errors, so the only conclusion
would be that the code you have is not identical to the code posted.
Google has been known to put in stray hyphens in the code (if you copy
it
from the posting) and it is possible wordwrap in the email may have
caused
problems. I didn't post any code that has :

& """".Item

nor did I have a line like

="="" &_ Format

that line was broken on two separate lines (for a reason).

it looks like the code has somehow been mangled in transmission.

here it is again:


Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, Cancel As _
Boolean)
With ThisWorkbook.Names
.Item("LastChange").RefersTo = "=""" & _
Format(Now(), "dd mmm yyyy") & """"
.Item("LastUser").RefersTo = Environ("username")
End With
End Sub

It works fine as written, so I don't know what to tell you.

If it still looks mangled, then I can send you a sample workbook
directly.

--
Regards,
Tom Ogilvy