Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
calculating user
Is there a function similar to NOW() that will show which user last made
changes to a file? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
calculating user
Here's a neat little ditty that Gord Dibben posted awhile back....
Private Sub Worksheet_Change(ByVal Target As Range) Target.Interior.ColorIndex = 0 On Error Resume Next Dim curComment As String curComment = "" curComment = Target.Comment.Text If curComment < "" Then curComment = curComment & Chr(10) Target.AddComment Target.Comment.Text Text:=curComment & _ ActiveWorkbook.Application.UserName & _ Chr(10) & " Rev. " & Format(Date, "yyyy-mm-dd ") & _ Format(Time, "hh:mm") ActiveCell.Comment.Visible = False 'comment perhaps should be resized End Sub Vaya con Dios, Chuck, CABGx3 "Beans" wrote: Is there a function similar to NOW() that will show which user last made changes to a file? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
calculating user
Have a look at the rest of the thread for possible revisions.
http://tinyurl.com/2bnsjv Gord On Tue, 12 Jun 2007 09:09:02 -0700, CLR wrote: Here's a neat little ditty that Gord Dibben posted awhile back.... Private Sub Worksheet_Change(ByVal Target As Range) Target.Interior.ColorIndex = 0 On Error Resume Next Dim curComment As String curComment = "" curComment = Target.Comment.Text If curComment < "" Then curComment = curComment & Chr(10) Target.AddComment Target.Comment.Text Text:=curComment & _ ActiveWorkbook.Application.UserName & _ Chr(10) & " Rev. " & Format(Date, "yyyy-mm-dd ") & _ Format(Time, "hh:mm") ActiveCell.Comment.Visible = False 'comment perhaps should be resized End Sub Vaya con Dios, Chuck, CABGx3 "Beans" wrote: Is there a function similar to NOW() that will show which user last made changes to a file? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
calculating user
In addition
You might want to change ActiveWorkbook.Application.UserName to Environ("UserName") which returns the login name. Gord On Tue, 12 Jun 2007 10:07:03 -0700, Gord Dibben <gorddibbATshawDOTca wrote: Have a look at the rest of the thread for possible revisions. http://tinyurl.com/2bnsjv Gord On Tue, 12 Jun 2007 09:09:02 -0700, CLR wrote: Here's a neat little ditty that Gord Dibben posted awhile back.... Private Sub Worksheet_Change(ByVal Target As Range) Target.Interior.ColorIndex = 0 On Error Resume Next Dim curComment As String curComment = "" curComment = Target.Comment.Text If curComment < "" Then curComment = curComment & Chr(10) Target.AddComment Target.Comment.Text Text:=curComment & _ ActiveWorkbook.Application.UserName & _ Chr(10) & " Rev. " & Format(Date, "yyyy-mm-dd ") & _ Format(Time, "hh:mm") ActiveCell.Comment.Visible = False 'comment perhaps should be resized End Sub Vaya con Dios, Chuck, CABGx3 "Beans" wrote: Is there a function similar to NOW() that will show which user last made changes to a file? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
calculating user
For anyone who might find this After a bit of tinkering, i modified the code to run whenever the workbook is saved.... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Call Dummy End Sub Public Sub Dummy() Dim curComment As String Dim aworksheet As Worksheet For Each aworksheet In Worksheets aworksheet.Activate With Cells(1, 1) .Interior.Color = RGB(0, 0, 175) On Error Resume Next curComment = "" curComment = .Comment.Text If curComment < "" Then curComment = curComment & Chr(10) .AddComment .Comment.Text Text:= _ ActiveWorkbook.Application.UserName & " was the last editor" & _ Chr(10) & " Rev. Date: " & Format(Date, "yyyy-mm-dd ") & _ " Time: " & Format(Time, "hh:mm") End With Next Worksheets(1).Activate End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User name | Excel Discussion (Misc queries) | |||
How do I format a cell, so the user must use a user calendar? | Excel Discussion (Misc queries) | |||
How do I format a cell, so the user must use a user calendar? | Excel Discussion (Misc queries) | |||
new user | New Users to Excel | |||
Calculating recurring date in following month, calculating # days in that period | Excel Worksheet Functions |