Code deleted in Macro Enabled 2007 workbook

[ecivon]

Hi there,

I created a spreadsheet which has been copied and used by various people over the last few months. An issue has reared its head twice during that time and I am very confused by it...

The spreadsheet fails to function as it was intended because all the code behind it has been erased.

The users are not developers and will not have the developers tab enabled on their PC. As far as I am aware all users are using excel 2007 which is the version I built it in.

I have been able to recreate this issue by saving as a non macro enabled file, but I was wondering if there was any other way this could occur?

 

[energman58]

I think if you open it "normally" in 2007 it will by default open as non-macro enabled file unless it is from a users trusted location or you explicitly enable the content by clicking. So if you have a file on a shared drive which is not a trusted location if your user then saves back on top of your original file then I think the macros can get wiped out. This is thanks to the infuriating security that MS built in to 2007 which really screwed up a lot of cool things you used to be able to do easily (thanks for the productivity enhancing tool Bill!)

You could make users open it as macro enabled file - by hiding all the sheets but a splash screen - but there are issues with some of the solutions that do this which still allow the user to break your code, see for example:

http://answers.microsoft.com/en-us/...y/cdf9885f-f4e3-4424-8fab-7adc541d1c6f?page=2

I THINK the final version in this post works but I may be wrong!