Open workbook in Design Mode

[akabraha]

Hi,

I got the following code from this forum which can open a workbook in Design mode by putting it in ThisWorkbook - Thank you very much.

Please Login or Register  to view this content.

Problem:
I am copying a sheet of workbook to create a new workbook using VBA. Original workbook should be in run mode and new workbook should be in design mode (because I don't want the codes (worksheet_change) in the sheet module to run while editing) . Can I insert above code into ThisWorkbook of new workbook while copying or is there any other way?

Thanks, Anil

[royUK]

I think you need to be more specific, what code don't you want to run? Why should the sheet need editing?

If you do need to edit without code running, don't enable macros

[akabraha]

Hope this will help:

Financial_analysis.xlsx - this is the original workbook with more than 50 sheets and is very huge. Many users across company access this tool to make financial analysis.

We want the user to save just the results sheet for future reference. Results sheet has macros which when saved alone as a new workbook, error out (compile error: sub or function not defined) when say I made some comments in a cell and pressed enter.

So when I let user save this results sheet through save as dialogue box using a macro, I want to save it with code to open it in design mode.

Thanks, Anil

[akabraha]

Thanks a lot for everyone's help, found useful code from the same forum which inserts the code into ThisWorkbook to enable opening it in design mode. Here is the code which worked for me if this helps anyone else.

Please Login or Register  to view this content.

Thanks again, Anil

[royUK]

You could simply create a function that checks the workbook name. Then use that function in your code so that if it's result is false the code does not run. Something like this

Please Login or Register  to view this content.

I think you will find that your code will require checking each user's settings

from Chip Pearson
First, you need to set an reference to the VBA Extensibililty library. The library contains the definitions of the objects that make up the VBProject. In the VBA editor, go the the Tools menu and choose References. In that dialog, scroll down to and check the entry for Microsoft Visual Basic For Applications Extensibility 5.3. If you do not set this reference, you will receive a User-defined type not defined compiler error.


Next, you need to enable programmatic access to the VBA Project. In Excel 2003 and earlier, go the Tools menu (in Excel, not in the VBA editor), choose Macros and then the Security item. In that dialog, click on the Trusted Publishers tab and check the Trust access to the Visual Basic Project setting.

In Excel 2007, click the Developer item on the main Ribbon and then click the Macro Security item in the Code panel. In that dialog, choose Macro Settings and check the Trust access to the VBA project object model.

The VBA Project that you are going to change with these procedures must be unlocked. There is no programmatic way to unlock a VBA project (other than using SendKeys). If the project is locked, you must manually unlock. Otherwise, the procedures will not work.