I have a workbook named 'File1_PA0.xls' and in it is my macro 'macro1'.
Upon opening the workbook, 'macro1' saves a copy (SaveCopyAs) of 'File1_PA0.xls' naming it 'File1_PA1.xls' (i.e. stepping the version number up by one).
From this point on, any 'save' event on my PA0 version will overwrite the PA1 file and leaves PA0 unchanged.
What 'macro1' does amongst other things is that it triggers SaveCopyAs upon any Workbook_BeforeSave event. After saving the higher version wb (i.e. PA1) the Workbook_BeforeSave event is cancelled leaving PA0 untouched.)
My aim is to leave the original file unchanged by saving any modifications by SaveCopyAs, always overriding the last existing PA revision i.e. PA1 in this case. Whne PA0 is eventually closed it becomes protected i.e. view only leaving the last revision (PA1) editable, with macro1 in it...
It all works perfectly fine until I protect 'macro1' with a password. Once the macro is protected, excel crashes upon any save event - as described above - and my Excel 2003 (Excel 11) rewards me with the folowing message:
-- removed inline image ---
"Run-time error '1004':
You cannot save to this file format when the VBA project is protected.
You may unprotect the VBA project by choosing the Properties command on the Visual Basic Editor's Tools menu, and entering the password for the active project."
Unfortunately the solution suggested is no option for me as dozens of users can open and edit my File1 workbook which is why I need the protection on macro1 that runs an essential revision control process.
Any ideas on a solution would be highly appreciated!
Upon opening the workbook, 'macro1' saves a copy (SaveCopyAs) of 'File1_PA0.xls' naming it 'File1_PA1.xls' (i.e. stepping the version number up by one).
From this point on, any 'save' event on my PA0 version will overwrite the PA1 file and leaves PA0 unchanged.
What 'macro1' does amongst other things is that it triggers SaveCopyAs upon any Workbook_BeforeSave event. After saving the higher version wb (i.e. PA1) the Workbook_BeforeSave event is cancelled leaving PA0 untouched.)
My aim is to leave the original file unchanged by saving any modifications by SaveCopyAs, always overriding the last existing PA revision i.e. PA1 in this case. Whne PA0 is eventually closed it becomes protected i.e. view only leaving the last revision (PA1) editable, with macro1 in it...
It all works perfectly fine until I protect 'macro1' with a password. Once the macro is protected, excel crashes upon any save event - as described above - and my Excel 2003 (Excel 11) rewards me with the folowing message:
-- removed inline image ---
"Run-time error '1004':
You cannot save to this file format when the VBA project is protected.
You may unprotect the VBA project by choosing the Properties command on the Visual Basic Editor's Tools menu, and entering the password for the active project."
Unfortunately the solution suggested is no option for me as dozens of users can open and edit my File1 workbook which is why I need the protection on macro1 that runs an essential revision control process.
Any ideas on a solution would be highly appreciated!
