Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

How to enable macros in Excel – Excelchat

We enable macros to provide an easy automation system for routine tasks. In this tutorial, we will learn how to enable macros in easy steps. We will also explore how to deal with macros in different security scenarios especially when we don’t trust the macro source.

Figure 1 – How to use macro enabled functions in excel

How to Enable Macros from the Message Bar

If we have an Excel document that contains macros, we will see a yellow message or security message saying Macros has been disabled. When we click on the Enable Content button, we can now use the macros in this document.

Figure 2 -How to enable macros in excel

Enable Macros using Backstage view

When we have an Excel document containing macros, we may also enable macros using Microsoft Office Backstage view. In this case, after we see the yellow Message bar, we will click on the File tab

And next, we will click on the Enable content shown in the Security Warning area.

Figure 3 –Enabling macros in Excel

We will see two features, Advanced options and Enable All Content. We will select Enable All content to ensure that the document’s active content is always available.

Figure 4 – Running macros in Excel

How to always run macros from a trusted worksheet

When we want to avoid the Macros Security warning every time, it is possible to enable this feature. But we must always ensure that all macros are from a trusted source. To begin:

  • We will go to File,
  • We will click Options, then Trust Center and press Trust Center Settings

Figure 5 – Using the Trust Center to enable Excel macro

  • Alternatively, we may click Trust Center through the Excel Backstage view session

Figure 6 -How to turn on macros in excel

  • Next, we will select Macro Settings from the Trust Center window

Figure 7 – Options for enabling macros in Excel

  • Now, we will click on enable all macros
  • From now, we can always enable macros without seeing the security warning

How to adjust macro settings on the Trust Center window

It is possible for us to adjust the macro settings, especially when we work with macro-enabled workbooks. We may visit the Trust Center window, as explained in the previous session.

Figure 8 – Enable macro in excel

Next, we will see these options explained below

  1. Disable all macros with notification: When we use this button, macros will be disabled but we will always see security alerts for it
  2. Disable all macros without notification: In this case, we may never know when we come across a macro file because all security alerts and macros will be disabled.
  3. Enable all macros: In this case, all macros will open without warning
  4. Disable all macros except digitally signed macros: In this case, we will see security alerts but only digitally trusted publisher macros will run on your Excel. If we have not trusted the publisher, it is also possible to enable it when we see the warning.
  5. Trust Access to the VBA project object model: By choosing this, we forbid or allow access to the VBA object model from an automation client.

How to run a macro even if all macros are disabled.

Even when we use the feature Disable all macros without notification, we can still use macros. This feature is possible in Excel 2007 to 2013. When we enable this feature, Excel will allow us to open macro-enabled files in particular locations where the security-warning feature will be omitted. In such locations, otherwise called trusted zones, macros will run automatically.

These trusted locations include:

Program Files\Microsoft Office\Templates

Program Files\Microsoft Office\Office12\Startup

Program Files\Microsoft Office\Office12\Library

Program Files\Microsoft Office\Office12\XLSTART

How to add a trusted location

We may also make trusted zones of our own by:

  • Go to File and select Options
  • Select Trust Center, then Trust Center settings and lastly Trusted locations

Figure 9 – Enable macro in trusted locations

 

  • Now we will click Add new location which will take us to Microsoft Office Trusted Location dialog box
  • We will click on Browse and pick any location on our PC

 

Figure 10 – How to add a location for enabling macros

Warning: We must make sure our chosen location is completely safe!

How to save a macro in a workbook

  • We will go to File, then Save As and click Browse to select a location
  • Next, we will click on Save As Type arrow and choose Excel Macro-Enabled Workbook.
  • We will tap Save

 

 

Figure 11 – Macro enabled workbooks

If we don’t select the Macro-Enabled Workbook selection, we will receive a warning. In this case, we will click No to make sure that we keep the macro.

How to disable macros in Excel 2013 to 2010

We can always disable macros by following the steps for running macros for a trusted worksheet. We will select Disable macros without notification or Disable all macros with notification.

Figure 12 – How to disable macros in Excel

How to use a special code to enable macros in a workbook

There are special codes we can use that will ensure that no one without rights to our Excel worksheets can turn on macros. With this code, all users must provide the code before they can run macros.

A good example is this code below:

'Force the explicit declaration of variables
Option Explicit

'Assign the name of the warning sheet to a constant
Const Warning As String = "Warning"

Private Sub Workbook_Open()

    'Turn off screen updating
    Application.ScreenUpdating = False
    
    'Call the ShowAllSheets routine
    Call ShowAllSheets
    
    'Set the workbook's Saved property to True
    Me.Saved = True
    
    'Turn on screen updating
    Application.ScreenUpdating = True
    
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    'Declare the variable
    Dim Ans As Integer
    
    'If the workbook's Saved property is False, emulate Excel's default save prompt
    If Me.Saved = False Then
        Do
            Ans = MsgBox("Do you want to save the changes you made to '" & _
                Me.Name & "'?", vbQuestion + vbYesNoCancel)
            Select Case Ans
                Case vbYes
                    Call CustomSave
                Case vbNo
                    Me.Saved = True
                Case vbCancel
                    Cancel = True
                    Exit Sub
            End Select
        Loop Until Me.Saved
    End If
    
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    'Cancel regular saving
    Cancel = True
    
    'Call the CustomSave routine
    Call CustomSave(SaveAsUI)
    
End Sub

Private Sub CustomSave(Optional SaveAs As Boolean)

    'Declare the variables
    Dim ActiveSht As Object
    Dim FileFormat As Variant
    Dim FileName As String
    Dim FileFilter As String
    Dim FilterIndex As Integer
    Dim Msg As String
    Dim Ans As Integer
    Dim OrigSaved As Boolean
    Dim WorkbookSaved As Boolean
    
    'Turn off screen updating
    Application.ScreenUpdating = False
    
    'Turn off events so that the BeforeSave event doesn't occur
    Application.EnableEvents = False
    
    'Assign the status of the workbook's Saved property to a variable
    OrigSaved = Me.Saved
    
    'Assign the active sheet to an object variable
    Set ActiveSht = ActiveSheet
    
    'Call the HideAllSheets routine
    Call HideAllSheets
    
    'Save workbook or prompt for SaveAs filename
    If SaveAs Or Len(Me.Path) = 0 Then
        If Val(Application.Version) < 12 Then
            FileFilter = "Microsoft Office Excel Workbook (*.xls), *.xls"
            FilterIndex = 1
        Else
            FileFilter = "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm, " & _
                "Excel 97-2003 Workbook (*.xls), *.xls"
            If Right(Me.Name, 4) = ".xls" Then
                FilterIndex = 2
            Else
                FilterIndex = 1
            End If
        End If
        Do
            FileName = Application.GetSaveAsFilename( _
                InitialFileName:=Me.Name, _
                FileFilter:=FileFilter, _
                FilterIndex:=FilterIndex, _
                Title:="SaveAs")
            If FileName = "False" Then Exit Do
            If IsLegalFilename(FileName) = False Then
                Msg = "The file name is invalid.  Try one of the "
                Msg = Msg & "following:" & vbCrLf & vbCrLf
                Msg = Msg & Chr(149) & " Make sure that the file name "
                Msg = Msg & "does not contain any" & vbCrLf
                Msg = Msg & "   of the following characters:  "
                Msg = Msg & "< > ? [ ] : | or *" & vbCrLf
                Msg = Msg & Chr(149) & " Make sure that the file/path "
                Msg = Msg & "name does not exceed" & vbCrLf
                Msg = Msg & "   more than 218 characters."
                MsgBox Msg, vbExclamation, "Invalid File Name"
            Else
                If Val(Application.Version) < 12 Then
                    FileFormat = -4143
                Else
                    If Right(FileName, 4) = ".xls" Then
                        FileFormat = 56
                    Else
                        FileFormat = 52
                    End If
                End If
                If Len(Dir(FileName)) = 0 Then
                    Application.DisplayAlerts = False
                    Me.SaveAs FileName, FileFormat
                    Application.DisplayAlerts = True
                    WorkbookSaved = True
                Else
                    Ans = MsgBox("'" & FileName & "' already exists.  " & _
                        "Do you want to replace it?", vbQuestion + vbYesNo, _
                        "Confirm Save As")
                    If Ans = vbYes Then
                        Application.DisplayAlerts = False
                        Me.SaveAs FileName, FileFormat
                        Application.DisplayAlerts = True
                        WorkbookSaved = True
                    End If
                End If
            End If
        Loop Until Me.Saved
    Else
        Application.DisplayAlerts = False
        Me.Save
        Application.DisplayAlerts = True
        WorkbookSaved = True
    End If
    
    'Call the ShowAllSheets routine
    Call ShowAllSheets
    
    'Activate the prior active sheet
    ActiveSht.Activate
    
    'Set the workbook's Saved property
    If WorkbookSaved Then
        Me.Saved = True
    Else
        Me.Saved = OrigSaved
    End If
    
    'Turn on screen updating
    Application.ScreenUpdating = True
    
    'Turn on events
    Application.EnableEvents = True
    
End Sub

Private Sub HideAllSheets()

    'Declare the variable
    Dim Sh As Object
    
    'Display the warning sheet
    Sheets(Warning).Visible = xlSheetVisible
    
    'Hide every sheet, except the warning sheet
    For Each Sh In Sheets
        If Sh.Name <> Warning Then
            Sh.Visible = xlSheetVeryHidden
        End If
    Next Sh
    
End Sub

Private Sub ShowAllSheets()

    'Declare the variable
    Dim Sh As Object
    
    'Display every sheet, except the warning sheet
    For Each Sh In Sheets
        If Sh.Name <> Warning Then
            Sh.Visible = xlSheetVisible
        End If
    Next Sh
    
    'Hide the warning sheet
    Sheets(Warning).Visible = xlSheetVeryHidden
    
End Sub

Private Function IsLegalFilename(ByVal fname As String) As Boolean
    Dim BadChars As Variant
    Dim i As Long
    If Len(fname) > 218 Then
        IsLegalFilename = False
        Exit Function
    Else
        BadChars = Array("\", "/", "<", ">", "?", "[", "]", ":", "|", "*", """")
        fname = GetFileName(fname)
        For i = LBound(BadChars) To UBound(BadChars)
            If InStr(1, fname, BadChars(i)) > 0 Then
                IsLegalFilename = False
                Exit Function
            End If
        Next i
    End If
    IsLegalFilename = True
End Function

Private Function GetFileName(ByVal FullName As String) As String
    Dim i As Long
    For i = Len(FullName) To 1 Step -1
        If Mid(FullName, i, 1) = Application.PathSeparator Then Exit For
    Next i
    GetFileName = Mid(FullName, i + 1)
End Function

How to add the above code to our workbook

  • We will add the code to our workbook
  • Next, we will create a new sheet named Warning
  • Now we will add the message to the worksheet asking the user to re-open the workbook and enable macros
  • We can now save our workbook

Note

Excel Macros are quite fantastic but we must be careful when using them. We can always create and use our own VBA codes at ease. But when using codes prepared by others, we should check the code first before enabling. VBA codes can be used by hackers with malicious intent.

Instant Connection to an Excel Expert

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc