I have a fair understanding of basic to intermediate VBA coding - here's the predicament I am having at work - I am responsible for maintaining this master excel file that consists of 35 tabs and macros and event procedures etc. - this file is used by the other team (more than 10 people) as the primary tool for carrying out their daily tasks - as the author i always keep an original copy of this file as a backup for any contingent event, and I put a copy of the file in the team folder for the team to use.

However, it sometimes happens that some of the team members would open this file in the team folder and make changes (they are told not to) as normal practice and accidentally save the changes withtout realising it - now that potentially creates an issue for the next user (good user) who would make a copy of this file and save it to their own folder and continute to work on with it (good practice) but they did not realise there has been data left in the workbook from the previous user - this kind of incident could create a disatrous consequency if it's left unnoticed.

I am trying to think up a way or series of codes that can resovle the issue - i just do not know which way to begin with - I was thinking of using SheetChange or Open (eg upon detecting any change then save as a new file in a different location) event - with that i ran into another issu- how i do ensure the subject event will not intervene other events that already exist in the workbook in the subsequential workbook?

any suggestion on structuring the code to accommedate this situation?

many thanks in advance

#VBA #event #savechange

as described in above

CodePudding user response：

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.

CodePudding user response：

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.