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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,590
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,435
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

mamboq

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

Board Regular
Joined
Nov 8, 2012
Messages
64
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

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
Top