Consulting

Results 1 to 11 of 11

Thread: Shared Excel File That Sends Daily Change Log

  1. #1

    Question Shared Excel File That Sends Daily Change Log

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


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

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

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

  2. #2
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    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 ?

  3. #3
    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.

  4. #4
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    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 ?

  5. #5
    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.

  6. #6
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    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.

  7. #7
    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.
    Attached Files Attached Files

  8. #8
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    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
    Attached Files Attached Files

  9. #9
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    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.

  10. #10
    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!

  11. #11
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    You are welcome.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •