Spreadsheet Version Control

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.

We’ve all been there.

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.

You changed something, Bob, I know you did (╯°□°)╯︵ ┻━┻

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.

Instructions

The setup

You will need to set up a minimum of three sheets:

  1. a “Welcome” sheet
    This will serve as a landing page for anyone opening the spreadsheet.
  2. a “Contents” sheet
    This will let the user know what the spreadsheet contents are and include our version control table.
  3. 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.

This is a message I use.

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)
  • Date
  • Description of changes
  • Author
  • 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.

This is how I laid out my Contents sheet

The Data Sheet

Put some data in it, idk. ¯\_(ツ)_/¯

The Macros

You will only need to work with two Workbook events:

  • Workbook_Open
  • Workbook_BeforeClose

Workbook_Open

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

Workbook_BeforeClose

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.