Excel 2007 Enable Macros Prompt vs Message Bar

mamboq

Board Regular
Joined
Nov 15, 2008
Messages
67
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?
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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.

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

Forum statistics

Threads
1,217,331
Messages
6,135,938
Members
449,973
Latest member
jarzack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top