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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,361
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,904
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,074
Messages
5,835,232
Members
430,349
Latest member
Gregori

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