How to Make Your Macros Not Cancel if You Click Again

The article looks at how to turn on macros in Excel, explains the nuts of macro security and shows how to conform the security settings to run VBA codes safely.

Like almost any technology, macros can be used for both expert and evil. Therefore, in Microsoft Excel, all macros are disabled by default. This tutorial covers different means to enable macros in Excel and explains potential risks associated with that.

• Macro security in Excel
• Enable macros for individual workbooks
  • Enable macros via security alert bar
  • Plow on macros in Backstage view
  • Trusted documents in Excel
• Enable macros temporarily (for electric current session)
• Enable all macros via Trust Center
• Excel macro settings
• Enable macros permanently in a trusted location
• Enabling macros programmatically with VBA
• Disable macros in Excel

Macro security in Excel

Before you go enable macros in your worksheets, it's important to understand how dangerous they tin mayhap exist.

Though VBA codes are very effective in automating complex and repetitious tasks, they are a significant source of risk from the security betoken of view. A malicious macro that you lot run unwittingly may damage or completely delete files on your hard drive, mess upwards your data, and fifty-fifty corrupt your Microsoft Role installation. For this reason, Excel'south default setting is to disable all macros with notification.

How to avoid these dangers? Just follow i simple rule: enable simply rubber macros – ones that you've written or recorded yourself, macros from trusted sources, and VBA codes that you have reviewed and fully understand.

How to enable macros for individual workbooks

There are 2 means to turn on macros for a certain file: directly from the workbook and through the Backstage view.

Enable macros via security warning bar

With the default macro settings, when you first open up a workbook containing macros, the yellow security alarm bar appears at the acme of the sheet correct under the ribbon:

If the Visual Bones Editor is open up at the time you are opening the file with macros, the Microsoft Excel Security Find will be displayed:

If you lot trust the source of the file and know that all the macros are secure, click the Enable Content or Enable Macros button. This will turn on the macros and make the file a trusted document. The next fourth dimension you lot open up the workbook, the security alert won't appear.

If the source of the file is unknown and you don't want to enable macros, y'all tin can click the 'X' push to close the security warning. The warning will disappear, simply macros volition remain disabled. Whatsoever attempt to run a macro will result in the post-obit message.

If y'all've disabled macros accidentally, simply re-open the workbook, and then click the Enable Content push on the alert bar.

Turn on macros in Backstage view

Another way to enable macros for a specific workbook is via the Office Backstage view. Here's how:

1. Click the File tab, and then click Info in the left menu.
2. In the Security Warning area, click Enable Content > Enable All Content.

Every bit with the previous method, your workbook will become a trusted certificate.

What you should know nigh trusted documents in Excel

Enabling macros through either a message bar or Backstage view makes the file a trusted document. However, some Excel files cannot be made trusted documents. For examples, files opened from an unsafe location such as the Temp Binder, or if the organisation ambassador has set the security policy in your organization to disable all macros without notification. In such cases, the macros are only enabled for a single time. On the next opening of the file, Excel will prompt you to enable the content once again. To avoid this, you can change your Trust Eye settings or save the file to a trusted location.

Once a particular workbook becomes a trusted document, there is no way to un-trust information technology. You can simply clear the Trusted Documents list. For this, do the following:

1. Click File > Options.
2. On the left side, select Trust Center, and so click Trust Center Settings.
3. In the Trust Heart dialog box, select Trusted Documents on the left.
4. Click Clear, and then click OK.

This will make all previously trusted files untrusted. When you open up such a file, the security alert will show upwardly.

Tip. If you do not desire to brand any documents trusted, tick the Disable Trusted Documents box. Y'all will yet be able to turn on macros on opening a workbook, but but for the current session.

How to enable macros for one session

In some situations, it stands to reason to enable macros but for a single fourth dimension. For example, when you received an Excel file with VBA lawmaking that you'd like to investigate, but you lot do not wish to make this file a trusted certificate.

The following instructions will guide you through the steps to enable macros for the duration that the file is open:

1. Click the File tab > Info.
2. In the Security Alert expanse, click Enable Content > Advanced Options.
3. In the Microsoft Office Security Options dialog box, select Enable content for this session, and click OK.
   

This turns on macros for in one case. When you lot close the workbook, then reopen it, the warning will announced once more.

How to enable macros in all workbooks via Trust Center

Microsoft Excel determines whether to allow or disallow VBA codes to run based on the macro setting selected in the Trust Eye, which is the place where you configure all the security settings for Excel.

To get macros enabled in all Excel workbooks by default, this is what you need to exercise:

1. Click the File tab, and then click Options at the very bottom of the left bar.
2. On the left-side pane, select Trust Center, so click Trust Eye Settings… .
   
3. In the Trust Center dialog box, click Macro Settings on the left, select Enable all macros and click OK.
   

Notes:

• The selection you set via the Trust Center becomes the new default macro setting and applies globally to all of your Excel files. If you want to enable macros for only specific workbooks, save them in a trusted location instead.
• Enabling all macros in all workbooks makes your computer vulnerable to potentially dangerous codes.

Excel macro settings explained

Bellow we will briefly explain all macro settings in the Trust Center to help you make an informed decision:

• Disable all macros without notification - all macros are disabled; no alarm volition evidence up. You won't be able to run any macros except the ones stored in trusted locations.
• Disable all macros with notification (default) - macros are disabled, merely y'all tin can enable them on a instance-by-case footing.
• Disable all macros except digitally signed macros – unsigned macros are disabled with notifications. Macros digitally signed with a special certificate by a trusted publisher are allowed to run. If you lot have not trusted the publisher, Excel will prompt you to trust the publisher and enable the macro.
• Enable all macros (not recommended) - all macros are immune to run, including potentially malicious codes.
• Trust access to the VBA project object model - this setting controls programmatic admission to the object model of Visual Basic for Applications. It's disabled by default to prevent unauthorized programs from changing your macros or building cocky-replicating harmful codes.

When irresolute the Trust Centre settings, please keep in mind that they apply just to Excel, not to all Role programs.

Enable macros permanently in a trusted location

Instead of manipulating the global macro settings, y'all tin configure Excel to trust specific locations on your figurer or local network. Any Excel file in a trusted location opens with macros enabled and without security warnings, even if the Disable all macros without notification option is selected in the Trust Eye settings. This lets you run macros in certain workbooks when all other Excel macros are disabled!

An instance of such files in the Personal Macro Workbook – all VBA codes in that workbook are bachelor for yous to use whenever you lot get-go Excel, regardless of your macro settings.

To view the current trusted locations or add a new one, deport out these steps:

1. Click File > Options.
2. On the left-hand pane, select Trust Center, then click Trust Centre Settings… .
3. In the Trust Center dialog box, select Trusted Locations on the left side. You will see a list of the default trusted locations. These locations are important for the correct work of Excel add together-ins, macros and templates, and should not be inverse. Technically, you can save your workbook to one of the Excel default locations, but it's improve to create your own one.
4. To set up your trusted location, click Add new location… .
   
5. In the Microsoft Office Trusted Locations dialog box, do the following:
   • Click the Browse push to navigate to the folder that you lot want to brand a trusted location.
   • If you wish any subfolder of the selected folder to be trusted as well, check the Subfolders of this location are also trusted box.
   • Type a short detect in the Clarification field (this can help you manage multiple locations) or leave it empty.
   • Click OK.

   

6. Click OK twice to close the remaining dialog boxes.

Done! You can now place your workbook with macros in your own trusted location and do non bother about Excel's security settings.

Tips and notes:

• Please be very conscientious when choosing a trusted location. Considering Excel automatically enables all macros in all workbooks that are stored in trusted locations, they become kind of loopholes in your security arrangement, vulnerable to macro viruses and hacking attacks. Never make any temporary binder a trusted source. Also, be cautious with the Documents folder, rather create a subfolder and designate information technology equally a trusted location.
• If you've mistakenly added a certain folder to the listing of trusted locations, select it and click the Remove button.

How to enable macros programmatically with VBA

On Excel forums, many people inquire if it is possible to enable macros programmatically on opening a workbook and disable them before exiting. The firsthand answer is "No, it'southward not possible". Because macro security is critical for the security of Excel, Microsoft designed whatsoever VBA lawmaking to only exist triggered past a user click.

However, when Microsoft closes a door, the user opens a window :) As a workaround, someone suggested a way to force the user to enable macros with a kind of "splash screen" or "instruction sheet". The general thought is as follows:

You write a code that makes all the worksheets but one very hidden (xlSheetVeryHidden). The visible sheet (splash screen) says something like "Please enable macros and re-open up the file" or provides more detailed instructions.

If macros are disabled, the user can merely see the "Splash Screen" worksheet; all other sheets are very hidden.

If macros are enabled, the code unhides all the sheets, and so makes them very subconscious again when the workbook closes.

The code examples can be found hither and here.

How to disable macros in Excel

As already mentioned, Excel'southward default setting is to disable macros with notification and allow users to enable them manually if they want to. If y'all'd like to disable all macros silently, without any notification, then choose the respective option (the first one) in the Trust Heart.

1. In your Excel, click the File tab > Options.
2. On the left-side pane, select Trust Center, and so click Trust Heart Settings… .
3. In the left menu, select Macro Settings, cull Disable all macros without notification, and click OK.
   

That'due south how you can enable and disable macros in Excel. I cheers for reading and hope to run across you on our blog side by side week!

You may also be interested in

cohenmatur2001.blogspot.com

Source: https://www.ablebits.com/office-addins-blog/2020/03/11/enable-disable-macros-excel/

Share this post