• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

How to log username of the users who change cells in a Shared workbook

URGENT need VB Script.

MS Excel 2003 - I have a number of worksheets containing numerous rows within a Shared workbook.

I want to log the username and date in a cell of a row of who has changed specified cells within that worksheet. Need to work on all work sheet.  

Also would like to hide LOG columns from Users so only people with a password can Unhide them
 see attached worksheet
Book1.xls
0
Shhhhsecretsquirrel
Asked:
Shhhhsecretsquirrel
  • 2
1 Solution
 
rspahitzCommented:
in VBA, you can simply add this to any existing worksheet:

 
Private mbChanging As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iRow As Integer
    Const LogColumn As Integer = 25

    If Target.Count = 1 Then
        If Not mbChanging Then
            mbChanging = True
            iRow = Target.Row
            
            Cells(iRow, LogColumn).Value = Environ("UserName")
            Cells(iRow, LogColumn).Font.Color = Cells(iRow, LogColumn).Interior.Color
            Cells(iRow, LogColumn + 1).Value = Now()
            Cells(iRow, LogColumn + 1).Font.Color = Cells(iRow, LogColumn + 1).Interior.Color
            mbChanging = False
        End If
    End If
End Sub

Open in new window


It will add the Log info to the 25th and 26 column of any changed cell.  (You may want to pick different columns if you have a wide sheet.
It will paint the text in white so the average person will not see it.

Is that close enough?
0
 
rspahitzCommented:
Possibly a better option is to enable Excel's built-in tracking feature.

In Excel 2007 that would be
Tab Review, group Changes, item Track Changes, sub item Highlight Changes.

The rest should be self-evident.
0
 
xtermieCommented:
I aggree with rspahitz...why use VB when you can use the built in features...also there is a History of changes that you can printout etc.
0
 
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now