Shared Excel File That Sends Daily Change Log

[SilverUnicrn]

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.

[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.