Save As non-macro enabled workbook

[Maleficent]

Hi all

I have a workbook which will be used by multiple users as a form to be completed (not a "form" in excel terms, just a workbook with a mixture of text boxes, combo boxes & check boxes). The workbook contains lots of code and will unhide certain rows/combo boxes etc subject to the users selections/data input.

once the user has completed the form, it is saved and attached to an email and sent to a mailbox.

The recipient needs to see the form as completed by the user. they do not need to use any of the macros and do not want to change any of the data. The workbook itself contains "on open" code which we do not want to execute when opened by the recipient (only when opened by the user).

So I thought the easiest thing to do would be to save it as an xlsx file instead of xlsm thus disabling all macros

However when I do this and try and open the saved file, I get an error message "Excel cannot open the file 'file name..' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."


Here is my code:

Please Login or Register  to view this content.

I'm new to VBA so sure I'm missing something really basic...

Thanks

[AlphaFrog]

If your worksheet code modules do not have any code in them...

Please Login or Register  to view this content.

...If they do...

Please Login or Register  to view this content.

[Maleficent]

Hi AlphaFrog thanks for the reply - I'm getting an error message "Run-time error '1004' Programmatic access to Visual Basic Project is not trusted"

Relating to this line:

Please Login or Register  to view this content.

any ideas?

thanks

[AlphaFrog]

For any Automation client to be able to access the VBA object model programmatically, the user running the code must explicitly grant access. To turn on access, the user must follow these steps.

Office 2003 and Office XP

• Open the Office 2003 or Office XP application in question. On the Tools menu, click Macro, and then click Security to open the Macro Security dialog box.
• On the Trusted Sources tab, click to select the Trust access to Visual Basic Project check box to turn on access.
• Click OK to apply the setting. You may need to restart the application for the code to run properly if you automate from a Component Object Model (COM) add-in or template.

Office 2007

• Open the 2007 Microsoft Office system application in question. Click the Microsoft Office button, and then click
• Application Options.
• Click the Trust Center tab, and then click
• Trust Center Settings.
• Click the Macro Settings tab, click to select the Trust access to the VBA project object model check box, and then click OK.
• Click OK.

[Maleficent]

Thanks. I'm using a company machine so the security settings are managed centrally. i managed to get what i needed by changing savecopyas to saveas. its always the smallest fixes that take forever to figure out! thank you for your help