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
- Disable all macros with notification: When we use this button, macros will be disabled but we will always see security alerts for it
- 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.
- Enable all macros: In this case, all macros will open without warning
- 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.
- 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