MS Excel 2010 Track Changes

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
LVL 35
Rob HensonFinance AnalystAsked:
Who is Participating?
Karen FalandaysTraining SpecialistCommented:
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

Look at using git for excel... I think there are some apps...?
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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.
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Rob HensonFinance AnalystAuthor Commented:
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.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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.
Rob HensonFinance AnalystAuthor Commented:
@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
Rob HensonFinance AnalystAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.