Shared Excel File That Sends Daily Change Log

[SilverUnicrn]

Our team is looking for a way to collaborate on a single Excel document. We need to be able to lock certain portions so that only a few have access and leave others open sot hat anyone can edit. We also need a way for the document to email a daily change log (possibly only for specific changes) so we know what changes were made, who made them, and when. I’m thinking the last part will probably need to be VBA Code?


We were thinking SharePoint or some similar platform. Google Docs is out as our company won’t allow access to it. We do have common folders where we could keep it that everyone can access.

We will also be using this file to pull the data into a larger file using VBA (the code is in the other file).

Just trying to figure out the best method at this point and see if there is even a way to make this happen. Any suggestions are greatly appreciated.

[Logit]

.
First challenge is to know that only one person will be able to work on the shared workbook at a time. You will find websites on Excel that explain more than one person can access an Excel Workbook at the same time BUT
inevitably there will be errors and data lost. So, one person at a time is the norm.

When you say lock out access to some areas for selected employees ... that is very doable as well. It is easier to lock out one or more sheets that are password protected rather than locking out a few cells or rows on a single sheet.
The latter is doable but requires more thought and coding (at least in my view).

Tracking who changes what, when, etc. is also very doable. I have several small examples that will work nicely for your project.

Does this help ?

[SilverUnicrn]

Yes, thank you, that absolutely helps. Can you give any direction as to where to begin looking to figure out exactly how to set this up?
I think we should be able to work with locking entire sheets.
Any recommendation on whether SharePoint or just a shared location would be better?
It sounds like tracking the changes is going to be the most challenging part of this.

[Logit]

My view is SharePoint is overkill and a waste of money. Place the file on your shared common folder.

Tracking changes will be "easy" and seamless. The users will never see it or know.

The first step is to know what your project looks like. What is the purpose, what is the goal, how do you perceive it should function?

This forum allows you to post code and the sample workbook for review.

So ... wadda got ?

[SilverUnicrn]

Thanks. Going to run with just the shared folder then.

Unfortunately, what I've got is a company that blocks any uploads from our computers...Sorry. But it's really just a straight forward (albeit very large) set of data, I haven't even stared to write any code for it as I was just beginning to research my options. All of the "fancy" stuff is in the file that the data from this file will eventually be incorporated into. But access to the larger file is extremely restricted, thus the need for a smaller file allowing greater access for more employees to enter and revise their data and a few select employees to monitor the changes via a change log within the (small) file and email notification when a change has been made, before finally incorporating them into the larger file. We need the change log to track every single change, but the email notification may only be necessary for certain high priority fields. The team is still debating that, and it will at least partially depend on how difficult the code is.

[Logit]

.
No problem.

The key to receiving assistance on this and other Forums is to present your project with "dummy info". Not the confidential stuff. You can plug in the confidential stuff after you get the workbook working.

Whenever you are ready to start just let us know.

[SilverUnicrn]

Okay, I hope this helps. So using a dummy file like the one attached, I need the "Change Log" to fill in as people are making changes. It would be great if the email would send a list of all changes with the current date at the end of each day...But no email would send on days when there were no changes.

I can protect the "Protected Data" with a password. And the "Combined Data" is what will be uploaded to the master file once the changes are reviewed.

[Logit]

Dim vOldVal 'Must be at top of module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim strUserName As String
    Dim xFormula As Boolean
    Dim xDate As Boolean
    Dim xHead As Range
    Dim xTitle As Range
    Set xHead = Sheets("Track_Changes").Range("B3:H3")
    strUserName = Application.UserName
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
    On Error Resume Next
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    If IsEmpty(vOldVal) Then vOldVal = "[empty cell]"
    xFormula = Target.HasFormula
    xDate = IsDate(Target)
    With Sheets("Track_Changes")
        .Unprotect Password:="Password"
        If .Range("B2") = vbNullString Then
            xHead = Array("DATE OF CHANGE", "TIME OF CHANGE", "SHEET NAME", "CELL CHANGED", "CHANGE BY", "OLD VALUE", "NEW VALUE")
            Sheets("Track_Changes").Columns(1).ColumnWidth = 3
            .Range("B1").Value = "Track Changes"
            .Range("B1").Font.Size = 18
            With xHead
                .Interior.Color = RGB(30, 139, 195)
                .Font.Color = vbWhite
                .Font.Bold = True
            End With
            With xHead.Borders(xlInsideVertical)
                .Color = vbWhite
                .Weight = xlMedium
            End With
        End If
        With .Cells(.Rows.Count, 2).End(xlUp)(2, 1)
            .Borders(xlInsideVertical).Color = RGB(255, 191, 191)
            .Borders(xlInsideVertical).Weight = xlMedium
            .Value = Date
            .Offset(0, 1) = Format(Now, "hh:mm:ss")
            .Offset(0, 2) = Target.Parent.Name
            .Offset(0, 3) = Target.Address
            .Offset(0, 4) = strUserName
            .Offset(0, 5) = vOldVal
            With .Offset(0, 6)
                If xFormula = True Then
                    .ClearComments
                    .AddComment.Text Text:="Cell is bold as value contains a formula"
                End If
                If xDate = True Then
                    .NumberFormat = "dd/mm/yyyy"
                End If
                .Value = Target
                .Font.Bold = xFormula
                If IsEmpty(Target) Then .Value = "[empty cell]"
            End With
        End With
        .Cells.Columns.AutoFit
        .Cells.Columns.HorizontalAlignment = xlLeft
        n = Sheets("Track_Changes").Range("B:B").Cells.SpecialCells(xlCellTypeConstants).Count - 1
        With Sheets("Track_Changes").Range("B4:H" & n + 2)
            .Borders(xlInsideHorizontal).Color = RGB(30, 139, 195)
            .Borders(xlInsideHorizontal).Weight = xlThin
            .Borders(xlInsideVertical).Color = RGB(200, 200, 200)
            .Borders(xlInsideVertical).Weight = xlThin
        End With
        .Protect Password:="Password"
    End With
    vOldVal = vbNullString
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    On Error GoTo 0
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    On Error Resume Next
    If Selection.Cells.Count > 1 Then Exit Sub 'Avoid runtime error 7
    vOldVal = Target
End Sub

[Logit]

.
For the email portion look at this resource. It is consider in VBA as the 'bible of vba emailing'.

https://www.rondebruin.nl/win/s1/outlook/mail.htm

See what you can do with Ron's examples. Come back for assistance.

[SilverUnicrn]

That's great, thank you! That email resource is good information to have. Let me work with it for a while and see if it can be tweaked easily for what I need. I appreciate your help!

[Logit]

.
You are welcome.