Ivan Howard
Active Member
- Joined
- Nov 10, 2004
- Messages
- 333
Hello all,
I have had a look on a few forums and can't find the answer to my question. Please can someone give me a hand?
I have Microsoft's Sensitivity Labelling software running and it requires that I select a label before I can use SaveAs for any new files. This poses as 'issue' in two scenarios:
1. When using VBA to create a new workbook as part of an automated process (e.g. exporting subsets, etc.), the process stops mid-point on each export so that the user can select a label. When running 30+ files, this was challenging but thankfully I have found a way to select a label mid-process using VBA so now, in this scenario, the code doesn't get interrupted and generates as many files as I want without pausing for user input. The link to this solution is here: Change Sensitivity Label Programatically
2. The second scenario is when I manually save a new workbook using SaveAs, I cannot get around the prompt to select a label. This is where I need a hand. I'd like to write some VBA that effectively applies a label (using my code from point 1 above) before the SaveAs process is run. As I do not want to have VBA in "ThisWorkbook" in every new file, I have tried using "Private Sub Workbook_BeforeSave..." in the PERSONAL.XLSB file, but, as I thought, it doesn't trigger when I save a new file, probably because it is trying to save the Personal.XLSB file and not the new file.
The question is, can I write some code that interrupts the SaveAs process on ALL new files - without having any VBA in those files?
Any help will be greatly appreciated.
Many thanks.
I have had a look on a few forums and can't find the answer to my question. Please can someone give me a hand?
I have Microsoft's Sensitivity Labelling software running and it requires that I select a label before I can use SaveAs for any new files. This poses as 'issue' in two scenarios:
1. When using VBA to create a new workbook as part of an automated process (e.g. exporting subsets, etc.), the process stops mid-point on each export so that the user can select a label. When running 30+ files, this was challenging but thankfully I have found a way to select a label mid-process using VBA so now, in this scenario, the code doesn't get interrupted and generates as many files as I want without pausing for user input. The link to this solution is here: Change Sensitivity Label Programatically
2. The second scenario is when I manually save a new workbook using SaveAs, I cannot get around the prompt to select a label. This is where I need a hand. I'd like to write some VBA that effectively applies a label (using my code from point 1 above) before the SaveAs process is run. As I do not want to have VBA in "ThisWorkbook" in every new file, I have tried using "Private Sub Workbook_BeforeSave..." in the PERSONAL.XLSB file, but, as I thought, it doesn't trigger when I save a new file, probably because it is trying to save the Personal.XLSB file and not the new file.
The question is, can I write some code that interrupts the SaveAs process on ALL new files - without having any VBA in those files?
Any help will be greatly appreciated.
Many thanks.
Last edited:
