Managing Version Control in Excel using Macros

If you work with spreadsheets that are regularly being updated and amended by multiple users then you will know something about version control. If you then add to this mix, the need to periodically distribute the updated spreadsheet for public viewing you will be more than well aware that Microsoft Excel isn’t really designed to handle version control very well.

I’m currently working with NHS contract proposals for the new financial year. The models are complex and the figures involved are high. We are up to version 20 already and the first cut hasn’t even been published. Between each issued version I know I will be inundated with emails asking what has changed and why.

I’m getting twitchy just thinking about it and I’m already dreaming about version control.

I’ve put together a few macros to enforce some rules for version control and to maintain an audit trail of changes and versions.

Methodology for Controlling Versions in Microsoft Excel
My spreadsheet includes tabs of data related to patient care, but it will work for any model that you wish to maintain a version trail for.

You need a tab for documenting the changes between each version. Mine is called Version Control but then I’m an accountant and don’t really do imagination.
On this sheet are two named ranges, VERSION and FILENAME (in B7 and B9 respectively) and two macro buttons for running the save options.

When the document is saved I want the user to overwrite the master document so that it always shows the latest version and I want them to save the file to a version folder and increment the filename to the next version number. So each save process creates one new file and overwrites another.

To ensure this happens just as I want, I need to prevent users from saving the spreadsheet in their own way. I do this by writing a procedure in the workbook object that will run whenever they attempt to save the document.

1
2
3
4
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
MsgBox "When you are happy with the changes, please go to the VERSION CONTROL tab and freeze the document to save"
End Sub

This directs the user back to the Version Control tab where they can either perform a FREEZE save or a temporary save. The temporary save is for convenience in case large changes are being made, it will save a single version with a temporary filename. The FREEZE save creates a new version and overwrites the master document, it also requires the user to enter some notes relating to the changes made.

Here’s the code for the FREEZE save:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
Sub FreezeWorkbook()
'Working in Excel 97-2010
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim wsVC As Worksheet
Dim Destwb As Workbook
Dim VersionFilePath As String
Dim VersionFileName As String
Dim MasterFilePath As String
Dim MasterFileName As String
Dim VersionDate As String
Dim Version As String
Dim CurrentVersion As String
Dim iLastRowVC As Long 'LAST ROW IN VERSION CONTROL SHEET
Dim Author As String
Dim Changes As String
Dim AmendRef As String

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'UPDATE THE VERSION CONTROL DETAILS
ActiveSheet.Unprotect
Set wsVC = Worksheets("VERSION CONTROL")
iLastRowVC = wsVC.Cells(Rows.Count, "B").End(xlUp).Row

'Current Version
CurrentVersion = wsVC.Range("a" & iLastRowVC).Value

'Insert version
Version = InputBox("Please enter the next incremental Version Number (Current Version = " & CurrentVersion & ")")
wsVC.Range("a" & iLastRowVC + 1).Value = Version

'Insert version date
VersionDate = Format(Now, "dd-mm-yy hh-mm-ss")
wsVC.Range("B" & iLastRowVC + 1).Value = VersionDate

Author = InputBox("Please enter your name")
wsVC.Range("c" & iLastRowVC + 1) = Author

Changes = InputBox("Please enter a brief description of changes made")
wsVC.Range("d" & iLastRowVC + 1) = Changes

AmendRef = InputBox("Enter the ref to the Amendments Document or N/A if none available")
wsVC.Range("e" & iLastRowVC + 1) = AmendRef

wsVC.Range("F" & iLastRowVC + 1).Value = Range("HLACTIVITY") 'Returns a the total activity value from a named range within the spreadsheet
wsVC.Range("G" & iLastRowVC + 1).Value = Range("HLVALUE") 'Returns a the total financial value from a named range within the spreadsheet

'Save the master workbook
Application.DisplayAlerts = False
Set Sourcewb = ActiveWorkbook

'Force the file extension to remain as Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143

'Overwrite the master report or create it if it doesn't yet exist
MasterFilePath = "c:\My Documents\2010-11 A&F Contract Templates\Masters\" 'Amend to suit your filing system
MasterFileName = Range("FILENAME") & " Master"

Range("VERSION").FormulaR1C1 = Version 'Overwrite the version number on the version control sheet

With Sourcewb
.SaveAs MasterFilePath & MasterFileName, FileFormat:=FileFormatNum, WriteResPassword:="" 'This ensures that the master is not password protected otherwise I get problems overwriting the file
End With
Application.DisplayAlerts = True

'save the version workbook
Set Sourcewb = ActiveWorkbook
'Force the file extension to remain as Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143

'Save the new workbook and close it
VersionFilePath = "c:\My Documents\A2010-11 A&F Contract Templates\Versions\RAP\"

VersionFileName = Range("FILENAME") & " " & Version
ActiveSheet.Protect

With Sourcewb
.SaveAs VersionFilePath & VersionFileName & FileExtStr, FileFormat:=FileFormatNum, WriteResPassword:="password" 'this sets password protection in place for the version spreadsheets
End With

MsgBox "New version saved as " & VersionFilePath & VersionFileName & " and Master Updated"

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

and the Temporary Save code which saves the file in the versions folder with a “Temp” suffix:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
Sub TempSave()
'Working in Excel 97-2010
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim TempVersion As String
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set Sourcewb = ActiveWorkbook

'Force the file extension to remain as Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143

ActiveSheet.Unprotect
TempFilePath = "c:\My Documents\2010-11 A&F Contract Templates\Versions\"
TempVersion = Format(Now, "yy-mm-dd hh-mm-ss")
TempFileName = "Temp " & Range("FILENAME") & " " & TempVersion
Range("VERSION").FormulaR1C1 = TempVersion
ActiveSheet.Protect

With Sourcewb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
End With

MsgBox "New version saved as " & TempFilePath & TempFileName

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

I’ve used the plugin Syntax Higlighter to display the VBA code. It looks great but unfortunately it doesn’t copy and paste well. You can download a working spreadsheet that includes the modules above if you want to copy them – Excel Version Control Example V1 the password to open is NCL.

As usual I have botched this macro together from many hints and tips found elsewhere on the interweb, this page from Ron de Bruin was particularly useful.

I think I need to add some loops into the input box code because users can choose not to enter any comments or an appropriate version number at the moment and I have no means of controlling the errors.

I’ve also had to run this without any password protection on the master file. If I password protect it the macro fails with a write protection error. I’m not sure if I ought to be able to get around this or whether it is not actually possible to save over a write protected file. Dunno.

Share the Love
Get Blog Updates

Excel Tutorial

  • N Sagar

    Hi,
    Thanks for this post. I am getting error at following point :
    MasterFileName = Range(“FILENAME”) & ” Master”

    Range(“VERSION”).FormulaR1C1 = Version
    Can you please help..
    Thanks,
    regards,

  • THiago

    Great man, can you upload the final version of your workbook with the code working? I tried to run here and didn’t work properly. Thank You

  • Duncan

    Hi, am trying to update this for excel 2010 but keep getting an range error Method ‘Range’ of ‘_Global’ failed in the Save new workbook and close it section of the sub line 60 MasterFileName = Range(“FILENAME”) & “Master”
    Not being a VB coder an a little lost any clue appreciated
    TIA
    Duncan

  • Cool Moxie

    I can see I was a bit lazy with my example spreadsheet. I’ve simply dumped you with the code that worked for my existing situation without demonstrating a working version control spreadsheet.

    In the code I have referenced some filepath locations, a named sheet and some named ranges. All of these would need to be amended for your specific requirements.

    In order to get my code working you could do the following but please note that you may need to alter the code according to your specific requirements, ie the HLACTIVITY and VALUE ranges may not be useful for you.

    Starting with

    Sub FreezeWorkbook()

    1. Name one of your sheets as VERSION CONTROL, which is referenced like this Set wsVC = Worksheets(“VERSION CONTROL”). This sheet will store all the details of changes, keeping track of the versions, who made the changes and what was done.

    2. Now you need to name some cells on the VERSION CONTROL sheet. Name B1 as FILENAME. Name B2 as VERSION. Name D1 as HLACTIVITY. Name D2 as HLVALUE

    3. These last two HLACTIVITY and HLVALUE in turn link to a value in the rest of the spreadsheet, perhaps a calculation. In my original case it held the contract value and associated activity and I want my version control sheet to show how these totals change between values.

    4. Change the MasterFilePath to a relevant location on your computer and make sure the folder exists.

    5. Change the VersionFilePath to a relevant location on your computer and make sure the folder exists.

    With Sub TempSave ()

    6. Change the TempFilePath to a relevant location on your computer and make sure the folder exists.

    That should do the trick.

    If I have time I’ll set up a working version and link from the main post.

  • Cool Moxie

    I’ve changed the linked file to one that will work, providing you change the file save locations to one that matches your machine.

  • sara

    What is the password to open the excel file?

  • Cool Moxie

    I’m really sorry that I left it with the password protection on – its NCL

  • Maria

    Hi! Thanks for the code. At this point though we are searching to some turnkey versioning tools. I was hoping if you could recommend something? We have a number of reporting spreadsheets circulating between departments, so we need:
    – version control for sheets and VBA projects
    – record who made the changes
    – compare versions with the original and each other
    – simple UI, suitable for non-programmers

    I came across this: http://www.pathio.com/, and this:
    https://xltools.net/version-control-for-vba-macros/. Maybe you have some others in mind.

  • Mattemer

    I know this is an old post, sorry to bother anyone. This was a great start for me, I tweaked a few things, such as the save automatically going up by .1 increment every time we Freeze the book (didn’t want the user to be able to just pick the increment), and I’m going to update it to enter the UserID as well for audit purposes.
    This is going to be a REALLY stupid question, but it looks like there are 3 things going on here. 1) The original version (mine is Version Control Test 4.xls), 2) the Versions saved out (in same folder I have a Version folder, they save there), then 3) the Master which is somehow saving to the next main folder “up”. What’s the purpose of the “original” version vs the Master version? Or did I mess the code up and that original version shouldn’t exist? Just the Master and the Versions?

  • Mattemer

    Fixed my own problem with 3 versions, but if a user closes out of this, then selects “Save”, they get the msgbox advising to Freeze when ready, but then the workbook closes. struggling with this piece.

  • http://whatapalaver.co.uk Dr Moxie

    Sorry, it’s an awfully long time since I wrote this and used the spreadsheets – I’m struggling to help.

  • Mattemer

    Sorry for the delay, but I figured out my issue, I had changed a snippet of code that was causing the issue and 3rd workbook. Thanks for all the effort here, this helped tremendously.

Excel Tutorial