?
Solved

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

Posted on 2011-05-01
5
Medium Priority
?
231 Views
Last Modified: 2012-05-11
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
Comment
Question by:Shhhhsecretsquirrel
  • 2
4 Comments
 
LVL 22

Accepted Solution

by:
rspahitz earned 2000 total points
ID: 35503025
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
 
LVL 22

Expert Comment

by:rspahitz
ID: 35503040
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
 
LVL 18

Expert Comment

by:xtermie
ID: 35503633
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
 
LVL 24

Expert Comment

by:Tracy
ID: 35877445
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question