MS Excel 2010 Track Changes

Posted on 2016-09-16
Last Modified: 2016-09-19
Hi all,

I am looking into the use of Track Changes across a number of Project Tracking workbooks.

Initially, the built in Track Changes feature seems to do exactly what we want; ie keep a history of changes made to the workbook; who, when and what.

I have come across some issues:

Shared Workbook - Enabling track Changes makes it a Shared Workbook. I have read (and cited) on numerous occasions that Shared Workbooks are notoriously unreliable. Has this improved with more recent versions of Excel? The workbooks involved will probably only be accessed by one person at a time so strictly speaking the shared feature is not required.

VBA editing - When in Shared mode the macros within the workbook can be used but cannot be edited. In order for the Administrator of the files (currently me!!) to go in and amend the macros from time to time, eg adding features or fixing issues, I have to switch off the Shared mode but this then deletes the History.

Too much detail - One of the macros in each of these workbooks allows the user to add rows to the table of data, using the macro ensures that all of the relevant formulas are copied into the new rows. The History sheet created by the Track Changes shows a line for the Insert of rows and then shows the detail of each row being populated, 20 rows of detail per row inserted. If the user inserts 10 rows which wouldn't be unrealistic that would be 200 rows of change in the History and then rows related to the user populating any of the 10 rows inserted.

Question 1:  can any of these be avoided or do I have to just put up with them?
Question2: I notice in the Track Changes wizard that you can ask for changes to a specific range. Can it be set that only a specific range is monitored? I guess I can get round this by Protecting areas that won't need changing. I have read elsewhere but don't recall where Protecting the sheet can be done in such a way that Macros can still be run even if they affect protected cells. How do I do this? I can get round this at the minute by starting the routine with ActiveSheet.Unprotect and finishing with ActiveSheet.Protect and the relevant parameters; I haven't applied a password as yet but might need to in future.

As always, thanks for your help.

Rob H
Question by:Rob Henson
LVL 15

Expert Comment

ID: 41802560
Look at using git for excel... I think there are some apps...?
LVL 19
ID: 41802565
Excel workbooks should not be shared. Better to use Access or another database to store information and each has their own copy of workbook with linked information. For tracking changes, you can use the Change event in Excel and write information to whatever is the back-end database.
LVL 32

Author Comment

by:Rob Henson
ID: 41804408
Dr Tribos - there is a feature within Excel itself; looking for advise from people that have used it, successfully or otherwise.

Crystal - I don't think the workbooks would end up being shared in the sense of multiple people using the file at the same time. Each project only has one Project Manager so should only need one person at a time. In addition to the PM the file would be accessed by Finance Admin which is me so I would hopefully recognise when the file is already open with the PM, I can use the Shared Workbook pop-up to check when I need to do Admin work to the file.

The files are saved on a shared network and it gets backed up every day so if the Shared feature does muck things up there will be something to return to.

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

LVL 19

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 250 total points
ID: 41804735
in that case, you will be able too see the old values and who made the change like comments, review what is different, and accept or reject them. It is not as sophisticated as tracking changes in Word but will let you see what has been done.
LVL 17

Accepted Solution

Karen Falandays earned 250 total points
ID: 41804745
Hi Rob, there are only a few ways that shared workbook can be used successfully. One is if there is a separate tab for each user, and no two users will ever edit the same sheet at the same time.

The other option is for you to turn on Shared workbooks and send a copy of the workbook to another user. The features will then be visible and when the workbook is sent back to you, you can merge the changes.

Otherwise, it is strongly advised against. As you mentioned, the features that will NOT work when workbook sharing is enabled are many: you cannot have graphs, macros, pivot tables, data tables, conditional formats, merged cells, hyperlinks, data validation, scenarios, outlines or subtotals. That is giving up a lot of powerful functionality, IMHO

LVL 32

Author Comment

by:Rob Henson
ID: 41804814
@kfalandays, thanks for your advice. As for features mentioned that will not work when shared:

  • Graphs - don't have any in these workbooks so can't test;
  • Macros - I can still use the macros but cannot edit them in VBE or any buttons assigned to them, as mentioned in question;
  • Pivot Tables - I do have a couple of these in each file and they don't work. This is probably going to be the stumbling block as I do need to use these;
  • Tables - I did have one in each but I can convert to range and use dynamic names instead;
  • Conditional Formats - I do have these and they still work;
  • Merged Cells - I don't have any of these in these particular workbooks, I try and avoid them anyway if I can;
  • Hyperlinks - I do have this and they still work when workbook is shared;
  • Data Validation - I do have this and it still works;
  • Scenarios - don't have any of these;
  • Outlines - I do have this and they still work when workbook is shared;
  • Subtotals - I don't have any of these but tested and they still work.
As mentioned, the stumbling block is going to be the use of Pivot Tables, this is still required and I don't know of another way of getting the same with the same simplicity. This on its own means I won't be able to use the Track Changes feature so will need to look for another way.

Rob H
LVL 32

Author Closing Comment

by:Rob Henson
ID: 41804820
Chose kfalandays comment as it points out that Pivot Tables are not available in Shared Workbooks. In this situation I need the use of those so cannot use the Track Changes feature.

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question