Excel 2007 Enable Macros Prompt vs Message Bar

[mamboq]

When a workbook contains macros, and the macro security level is set to 'disable all macros with notification', Excel 2007 shows the Options button in the message bar, but older versions display the prompt before the workbook is opened.

After reading a number of posts on the internet, I initially thought that the only way to enable macros in Excel 2007 (with this level of security) was the Options button on the message bar. But recently, to my amazement, Excel 2007 did actually display the prompt when I opened a workbook with macros. Since then it has been inconsistent - most of the time it just displays the Options button, but sometimes it displays the prompt before the workbook is opened.

Is there any way to make Excel 2007 always display the prompt before the workbook is opened, as it did in older versions?

 

[Peter_SSs]

But recently, to my amazement, Excel 2007 did actually display the prompt when I opened a workbook with macros. Since then it has been inconsistent - most of the time it just displays the Options button, but sometimes it displays the prompt before the workbook is opened.

Not answering your main question, but see if this sheds any light on the inconsistency issue above. Open the workbook in question
a) With the Visual Basic window already open
b) With the Visual Basic window closed.

 

[JoeMo]

When a workbook contains macros, and the macro security level is set to 'disable all macros with notification', Excel 2007 shows the Options button in the message bar, but older versions display the prompt before the workbook is opened.

After reading a number of posts on the internet, I initially thought that the only way to enable macros in Excel 2007 (with this level of security) was the Options button on the message bar. But recently, to my amazement, Excel 2007 did actually display the prompt when I opened a workbook with macros. Since then it has been inconsistent - most of the time it just displays the Options button, but sometimes it displays the prompt before the workbook is opened.

Is there any way to make Excel 2007 always display the prompt before the workbook is opened, as it did in older versions?

I took a guess (below) then saw Peter's answer and ran a quick test using his suggestion - I believe Peter is correct. Ignore my reply below.

I'm guessing that when you get the old prompt you are opening a file with a '.xls' extension using Excel 2007. Whereas the option button appears when you open a file with a '.xlsm' or '.xlsb' extension. If you want the prompt and don't mind saving files in a pre-2007-compatible format, then save all files with a .xls extension.

 

[mamboq]

Thanks guys...

Yes, that must be why the dialog box was popping up sometimes and not others - when the visual basic editor is closed the dialog box doesn't appear, but does when the editor is open. So I'll just try to keep it open before opening workbooks.

Tim

 

[Galley]

To ensure that users enable macros, I use a large brightly-colored graphic that says "Macros must be enabled!" and displays the steps required. The following code hides the graphic once macros are enabled.

Private Sub Workbook_Open()
'Hides the "Macros must be enabled" graphic when macros are enabled.
ActiveSheet.Pictures("Picture 5").Visible = False
End Sub