Can anybody guide me on a way to do this.

I have a master file that contains configuration data for a number of tools I've created for my colleagues. Since this configuration data has to be updatable, each tool needs to update it's own copy of the data if the master data is newer. This should happen on launch.

The master data must only be updated in a particular fashion, so I have created a userform based UI to allow users to do this. It seems reasonable to keep this code in the master data itself. The UI should launch if the master data file is opened directly (ie. in windows explorer or through Excel's open menu) or if a user clicks an "Edit master data" button in one of the tools, but NOT when a tool is just checking/updating its own data.

If a tool's "Edit master data" button is clicked, it needs to launch the UI in the master data, and after the UI session is closed by the user, the tool then needs to check if the master data was updated during that session and update it's own copy if it was. So a tool's "Edit master data" routine will need to halt it's own execution until the UI in the master is finished running.

The master data is located on a network drive and needs to be accessible and updatable by any colleague from any tool at anytime, so a tool can't just open the master data at launch and keep it open. It must only open and close the master data to check for updates or to edit the data. The rest of the time the tool will use its own copy of the data for its various procedures.

At present, the UI is launched in the Workbook_Open event of the master data workbook. When a tool is only checking/updating it's data, it's code sets Application.EnableEvents to False before the master workbook is opened, so the Workbook_Open event doesn't fire and the UI is not launched.

The problem I'm having with this approach is that when EnableEvents is set to True and the UI in the master file does launch (in the case of a user clicking a tool's "Edit master data" button), execution of the code in the tool doesn't just halt, it stops all together. Nothing further is done after the UI is closed by the user. Execution should return to the tool which should continue to check if the master data was updated during that UI session, and replace it's own data if it was.

If you need anything above clarifying, please ask me. I'm looking for a solution and I'm open to all ideas, even if it means scrapping the above approach in favour of something else you can think of.

Many thanks in advance if you're able to help.