#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15
Default calculating user

Is there a function similar to NOW() that will show which user last made
changes to a file?
  #2   Report Post  
Posted to microsoft.public.excel.misc
CLR CLR is offline
external usenet poster
 
Posts: 1,998
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
User name mkraizrool Excel Discussion (Misc queries) 7 February 9th 09 10:43 PM
How do I format a cell, so the user must use a user calendar? nathan Excel Discussion (Misc queries) 1 January 16th 06 07:40 PM
How do I format a cell, so the user must use a user calendar? nathan Excel Discussion (Misc queries) 0 January 16th 06 06:26 PM
new user letterofcredit New Users to Excel 3 November 1st 05 08:32 PM
Calculating recurring date in following month, calculating # days in that period Walterius Excel Worksheet Functions 6 June 4th 05 11:21 PM


All times are GMT +1. The time now is 12:33 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"