Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I make a picture change based on input from a user? | Excel Discussion (Misc queries) | |||
User Defined Functions - Help Text - Make it Easy for the User | Excel Programming | |||
Make Sure User Selects 3 Cells | Excel Programming | |||
Make a user form 'un-closeable' | Excel Programming | |||
How to: Make user click End User License Agreement acceptance | Excel Programming |