Have you ever opened a spreadsheet on a network drive or Sharepoint and thought, “Someone’s been
sleeping in my bed messing with my data!”?
I know I have.
Fortunately, the cloud has offered us a way to restore previous versions of our spreadsheets quickly and easily. Two clicks and boom – your spreadsheet as it was the last time you looked. Unfortunately, the cloud does not give us a way to know if changes were important or valid.
One way to address this problem is to include a change register on a coversheet within the spreadsheet. This technique introduces the next enemy of the spreadsheet: The Lazy Coworker.
Bob cares not of your efforts to maintain good data management. He laughs in the face of danger and types only with his index fingers – you know the Bob I mean. But how can we protect ourselves against Bob? Although we can’t defend against the malicious, this approach will prompt any lazy users to tell us what they’re up to.
This method requires a basic understanding of Excel VBA. If you are not confident in your coding skills, you can download the spreadsheet instead.
You will need to set up a minimum of three sheets:
- a “Welcome” sheet
This will serve as a landing page for anyone opening the spreadsheet.
- a “Contents” sheet
This will let the user know what the spreadsheet contents are and include our version control table.
- a worksheet for your data
Any name will do for this one. In the template, I called it “Sheet”. My creativity is overwhelming, I know.
The “Welcome” Sheet
All we really need on this one is a message to the user that they cannot access the spreadsheet online or without macros enabled.
If you use Sharepoint or OneDrive, make sure to name this cell – I called mine “warning”. Also, make sure to name at least one other cell in the spreadsheet. I always have a cell called “workbook_title” so I can refine headings on all worksheets at once.
The reason we needed to do that was to enable Browser View Options (accessed from the File >>> Info menu). From here, you want to select “Items in the workbook” from the dropdown menu and then select “warning”. This will ensure that when someone opens the spreadsheet online all that they can see is that message.
The Contents Sheet
This one is a little more complicated. Within this spreadsheet, you will need to make a table for your version control. I use the following headings where I have bolded the ones necessary for this tutorial:
- Issue (eg: 1.0.0)
- Description of changes
- Checked by
- Approved by
- Issued to (the person that the spreadsheet was shared with)
- Organisation (workplace of the person above)
Within the sheet, I also like to include a Contents list that outlines all of the worksheets and their functions. Within the context of this tutorial, this will be important to include because I use the “Contents” heading to know when I have found the last row of my version control table.
The Data Sheet
Put some data in it, idk. ¯\_(ツ)_/¯
You will only need to work with two Workbook events:
Private Sub Workbook_Open() Dim ws As Worksheet Action = MsgBox("Are you making any changes to the spreadsheet?", vbYesNo, "Edit Check") 'Prevents flicker and speeds up execution Application.ScreenUpdating = False 'Make the workbook read only if the user selects no If Action = 7 Then ThisWorkbook.ChangeFileAccess xlReadOnly 'Iterate through each sheet to hide the "welcome" sheet and expose all others For Each ws In ActiveWorkbook.Worksheets If ws.Name = "Welcome" Then ws.Visible = xlSheetVeryHidden Else ws.Visible = xlSheetVisible End If Next 'If the user intends to edit, add a row to the version control table If Action = 6 Then 'Here, the 10,000 is just a large number. Notice that I am looking for "Contents" in column 2. You may need to edit this to suit your layout. Similarly, you may need to change the "Row - 2" depending on how many rows apart the "Contents" heading is. For Row = 10 To 10000 Step 1 If Worksheets("Contents").Cells(Row, 2).value = "Contents" Then Worksheets("Contents").Rows(Row - 2).Insert xlShiftDown Exit For End If Next 'This adds 1 to the previous version Worksheets("Contents").Range("C" & Row - 2).value = Mid(Worksheets("Contents").Range("C" & Row - 3).value, 1, InStrRev(Worksheets("Contents").Range("C" & Row - 3).value, ".")) & Int(Right(Worksheets("Contents").Range("C" & Row - 3).value, Len(Worksheets("Contents").Range("C" & Row - 3).value) - InStrRev(Worksheets("Contents").Range("C" & Row - 3).value, "."))) + 1 'Adds the date to the new row Worksheets("Contents").Range("D" & Row - 2).value = Date 'Adds the current author name to the row (beat that, Bob!) Worksheets("Contents").Range("F" & Row - 2).value = Application.UserName 'Selects the "description of changes" column for the user to edit Application.Goto ThisWorkbook.Worksheets("Contents").Range("E" & Row - 2) End If 'If the user is in read-only mode, I send them to a cell I want them to look at (I have just used an arbitrary cell here). If Action = 7 Then Application.Goto ThisWorkbook.Worksheets("Contents").Range("A1") 'Don't forget to switch this guy back on! Application.ScreenUpdating = True End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim ws As Worksheet 'We only need to worry about this stuff if the user made edits If Not ThisWorkbook.ReadOnly Then 'Prevents flicker and speeds up execution Application.ScreenUpdating = False 'We have to first make the sheet visible and then hide the others so there isn't a time where ALL sheets are hidden. For Each ws In ActiveWorkbook.Worksheets If ws.Name = "Welcome" Then ws.Visible = xlSheetVisible 'Selects the cell we named warning, you may need to change this to whatever you named your welcome cell. Application.Goto Range("warning") Exit For End If Next 'Hide all of the other sheets For Each ws In ActiveWorkbook.Worksheets If ws.Name <> "Welcome" Then ws.Visible = xlSheetVeryHidden Next 'Don't forget this guy! Application.ScreenUpdating = True 'I learned this bit from trial and error. I found that Excel did not have enough time to load the "Welcome" tab before the program exited. This would lead to the next user not seeing the "Welcome" tab, but whatever the last tab was opened. boxAns = MsgBox("Close the spreadsheet now?", vbYesNo, "Confirm exit") If boxAns = vbYes Then Application.Wait (Now() + TimeValue("0:00:01")) ThisWorkbook.Save Else 'If the user decides to keep using the spreadsheet, we just have to unwind all of the hiding we did. Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.Name = "Welcome" Then ws.Visible = xlSheetVeryHidden Else ws.Visible = xlSheetVisible Next Application.Goto Range("workbook_title") Application.ScreenUpdating = True Cancel = True End If End If End Sub
And that’s it! You should now have a spreadsheet that frustrates its viewers into submitting their nefarious plans for your data into a version control table.