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

Posted on 2011-05-01
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
Question by:Shhhhsecretsquirrel
    LVL 22

    Accepted Solution

    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?
    LVL 22

    Expert Comment

    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.
    LVL 16

    Expert Comment

    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.
    LVL 24

    Expert Comment

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
    Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now