1

I maintain a master Excel file used by a team. I keep an original of this file as a backup, and I put a copy in the team folder.

Sometimes a team member opens this file in the team folder, make changes, and accidentally save the changes. That potentially creates an issue for the next user who would make a copy of this file and save it to their own folder to work on it, but not realise data has been left in the workbook from the previous user.

I am trying to think up a way or code that can resolve the issue.
I was thinking of using SheetChange or Open event (e.g. upon detecting any change other than save as a new file in a different location). With that I ran into another issue. How do I ensure the event will not interfere with other events that exist in the workbook or in the subsequent workbook?

Community
  • 1
  • 1
Makubexho PC
  • 17
  • 4

2 Answers2

0

Save the file as an Excel Macro-Enabled Template (.xltm) file.
This way, on double-clicking the file (as you would to open any other file), it creates a new file and will not automatically overwrite the old file when saving.
Instead of taking copies of the file, your users simply have to 'open' the file then later save as whatever they need to.

Spencer Barnes
  • 2,809
  • 1
  • 7
  • 26
  • the master excel file is macro-enabled. can you please explain what you mean by "user simply have to 'open' the file"? – Makubexho PC Dec 19 '22 at 12:42
  • Opening a template file doesn't actually open the file for editing, instead it creates a new workbook (named "book1" by default) based on the template. From what you described of your usage, the users should never edit the original file so it really is behaving as a template for them, which is why I'm recommending you save as a template file format. – Spencer Barnes Dec 19 '22 at 12:49
  • Excel Macro-Enabled Template it is! this saves me so much time in finding a way with coding. so simple yet so satisfying. Thank you so much - I have learned something new of Excel today. – Makubexho PC Dec 19 '22 at 13:01
  • when i tested your method of creating a template on my personal laptop - my template excel workbook will prompt me upon saving any change i have made to the template - however this does not work at all on my company laptop, like i save my mast file as macro-enabled template then open it then make changes then hit save - it saved with the changes without prompting me to save as seperate file. why? – Makubexho PC Dec 19 '22 at 21:59
  • Sounds like a separate question, possibly for [su]. You have double-checked that the original was an xltm, and the new file once saved has overwritten the old one? It's working fine for me. – Spencer Barnes Dec 20 '22 at 08:22
0

I would keep the master copy well hidden from them.

Then, consider putting passwords on sheets they MUST not change.

Or, consider sub-master files for the detail that each team can change and then your master file can link to those sub-files to get the latest data.

I had a project to manage that had 6 team members. Gave them each their own file and linked to their data. Also passworded the functions so they could not change or delete them.

Solar Mike
  • 7,156
  • 4
  • 17
  • 32
  • the file i put in the team folder for other to use contains latest rates and contracted service provider details and sheets for breaking down costs etc. everyday these team members would go in the team folder and make a copy of the file and save it to their own location and work on it (the proper use of the file) - however it sometimes happens someone opens the file in the team folder and make changes and hit save that's a big no no as that creates an issue for the next user. I dont want that happens. there are 35 functionable tabs in this master workbook. putting a password would be a nusance – Makubexho PC Dec 19 '22 at 21:55
  • It’s a nuisance they change things they should not… – Solar Mike Dec 20 '22 at 05:01