Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


MS Excel 2010 Track Changes

Posted on 2016-09-16
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 15

Expert Comment

ID: 41802560
Look at using git for excel... I think there are some apps...?
LVL 22
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 33

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.


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 22

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 1000 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 18

Accepted Solution

Karen Falandays earned 1000 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 33

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 33

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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‚Ķ

618 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