Enable Macros when workbook is created

[jomili]

I use macros in one workbook (A) to create another workbook (B) from Template (C). Template (C) has macros as part of it's makeup. Specifically, there's a macro that's triggered by the Worksheet Activate of a particular sheet ("SheetSpecial").

My problem is that after creation of B, B gets saved but remains open, A and C close without saving changes, but the Worksheet Activate macros in B don't work unless I close and reopen the workbook. How can I get them to be working from inception? I need this because my users will want to work on the workbook as soon as it's created.

Thanks in advance,
John

[Steffen Thomsen]

Then you have to save the file in a location trusted by excel and save the workbook as .xlsm before activating it.

[jomili]

I'm using 2003, so .xlsm isn't really an option.

[Steffen Thomsen]

What code are you using for the save as?

And does it work when the location is trusted?

[jomili]

Here's my macro for my initial save:

'This one opens the "Projections" template
    Workbooks.Open _
("\\12AUST1001FS01\SHARE10011\Budget\SOBUDGET\CPS\Reports\Position\Templates\Projections.xls"), ReadOnly:=True, UpdateLinks:=True
    
    'Now we name the workbook and save it to our desktop
        Application.DisplayAlerts = False
        Dim WshShell As Object
        Set WshShell = CreateObject("WScript.Shell")
        DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
        ActiveWorkbook.SaveAs DTAddress & "2BDeleted"

. Following this I do heavy manipulation to the workbook. At the end of things, I do a save and then close out the workbook that initiated everything:

Run "PivotUpdate"
   TwoB.Save
   
   Workbooks("MFR Projection Tool.xls").Close False
   End Sub

I'm not familiar with "Trusting" a location. What do I have to do to make a location "Trusted" by Excel?

[Steffen Thomsen]

Hi,

You can read a little about it here

[jomili]

Okay, I read over the article. The gist of it for me is this statement:

The box titled "At startup, open all files in" shows the trusted location.

For my personal setup, "Open All Files In" is blank. I have no idea what it would be set up as for my users, who stretch across the length and breadth of Texas.

On Excel Banter (http://www.excelbanter.com/showthread.php?p=964890), I found this from Gord Dibben:

Excel 2003 does not have "Trusted Locations"

So, where does that leave us?

[Steffen Thomsen]

Upgrade to excel 2010

Im really not sure then, maby someone else has the answer for this then.

[jomili]

Thanks for trying. I'd LOVE to move up to 2010, but I work for state government. We'll probably move to 2010 in around 2015.

[Steffen Thomsen]

The year is almost 2012, when you upgrade next 2010 wont even be sold

[royUK]

Macros will run in the new workbook. The problem is you are running code from Workbook A and closing it. Once it is closed then any code in it will not run. You need a way to activate the specific sheet in the new workbook before A is closed.

Add a procedure in the new workbook that is called from A, this code should close A & activate the sheet

[jomili]

Roy,
I understand in theory, but not in practice. You're saying I should have a macro in B that is called from A, then when I close A the macros in B will be active. I'm not sure how to call a macro from the new workbook. Do I put "Run" before the macro name, or is there another trick?

[romperstomper]

The only reason I can think of for the behaviour you describe, would be that you disable events somewhere along the line and they don't get reset.

[jomili]

And that's a good reason!

I checked, and that was exactly what the problem was. I put "Enable Events" at the end of all my code, and VOILA! it worked just fine.

So easy, once you know the problem. Thanks for pointing it out.

Thanks everyone for your help on this one. I learned some neat stuff. And a special thanks to RomperStomper for going straight to the source of the problem to find a solution.

God bless you, every one!