Solved: problem with running operning script Excel 2007 due to Enable Macros...

[sunilmulay]

Hi there

I have the following code in my spreadsheet
[VBA]
Option Explicit

Private Sub Workbook_Open()
'Turn off screen updates
Application.ScreenUpdating = False

'Activate cell A1 on the Dashboard sheet at startup
With Worksheets("PP")
.Activate
.Range("D5").Select
End With

'Restore screen updates
Application.ScreenUpdating = True
End Sub
[/VBA]
What I found when opening this spreadsheet up in Excel 2007 is that because it doesn't prompt you to Enable macros like version 2003 does, it does not automatically execute the command. ONce you are in the file, if you enable macros it doesn't run the above script automatically.

How can I get around this problem?
ThanksSunil

[Demosthine]

Good Evening.

The entire Force Macro Enable issue is a hot topic and you can find literally dozens of solutions on the Web. Ken Puhls has a pretty good example on his website or his Knowledge Base Article.

Alternately, the only sure-fire way to ensure Macros are Enabled seems to be to use a VBScript File to open it. This may not be an option depending on your System Administrators and their restrictions, but it may be an option. You just have to beat it into the Users' heads with a club (or other blunt object), that the .vbs file is the only file they are to click on to open the Workbook. If you can use a Shortcut for them to access it, change the Shortcut Icon to the Excel Icon and rename it WorkbookName.xls.

Let us know if you need more help with either of these.
Scott

[sunilmulay]

Thanks - I'll try Ken Puhls example...
Not sure if enabling macros after getting to the Welcome Page in Excel 2007 would automatically kick off the Private Sub Workbook_Open() marco though... I may have to build in my macro above into Ken's code....
There's more code in my macro than I've displayed here (haven't got it to hand), but it prompts the user for an activation key, and if it doesn't match with what's on a hidden sheet, it closes the workbook down. I need that macro to run as soon as the user enables Macros. This works well in Excel 2003, because it asks you to enable macros before actually opening the file. But in Excel 2007, it opens the file with macros disabled and then gives you the option of enabling them....

Thanks
Sunil