Solved

MS Excel 2010 Track Changes

Posted on 2016-09-16
7
85 Views
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
0
Comment
Question by:Rob Henson
[X]
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
7 Comments
 
LVL 15

Expert Comment

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

Thanks
Rob
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 20

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.
0
 
LVL 18

Accepted Solution

by:
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

kfalandays
0
 
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.

Thanks,
Rob H
0
 
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

738 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