Forcing Your Clients to Enable Excel Macros

By Michael Alexander

To use any macro in Excel, your users must enable macros. That is, they must give Excel permission to run macros in their environment. To enable macros, users click the Enable Content button in the security warning that pops up above the formula bar.

image0.jpg

The bottom line is that you can write all the fancy macros in the world, but they will never run unless your users enable macros. This begs the question; can you force users to enable macros? The answer is yes – with a little trickery.

The idea is relatively simple. You create a sheet named, say, START. That sheet contains only a simple warning stating that macros must be enabled. You then hide all sheets in the workbook except for that START sheet. Finally, you write a simple macro that unhides all sheets when the workbook is opened.

image1.jpg

At this point, when the workbook is Opened, Excel will ask the user to enable macros. Users will be forced to do so because all they will see is your Start sheet. The other sheets will be hidden!

To make this technique work, you need two macros: One macro hides all but the START sheet when the workbook closes, and another macro that unhides all but the START sheet when the workbook opens.

First, tackle the actions that need to happen when the workbook closes. :

  1. Activate Visual Basic Editor by pressing ALT+F11 on your keyboard

  2. In the Project window, find your project/workbook name and click the plus sign next to it to see all the sheets.

  3. Click ThisWorkbook.

  4. In the Event drop-down box, select the BeforeClose event.

    image2.jpg

  5. Type or paste the following code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Step 1: Declare your variables
  Dim ws As Worksheet
'Step 2: Unhide the Starting Sheet
  Sheets("START").Visible = xlSheetVisible
'Step 3: Start looping through all worksheets
  For Each ws In ThisWorkbook.Worksheets
'Step 4: Check each worksheet name
  If ws.Name <> "START" Then
'Step 5: Hide the sheet
  ws.Visible = xlVeryHidden
  End If
'Step 6: Loop to next worksheet
  Next ws
'Step 7: Save the workbook
  ActiveWorkbook.Save
End Sub

In Step 1, you declare an object called ws to create a memory container for each worksheet you will loop through.

In Step 2, you ensure that the START sheet is visible.

In Step 3, you start the looping, telling Excel that you want to evaluate all worksheets in this workbook.

In Step 4, you simply compare the name START to the sheet that is currently being looped. This step ensures that the actions that come next are applied to all sheets except the START sheet.

If the sheet names are different, in Step 5 you hide the sheet by using the xlVeryHidden property. This property not only hides the sheet but also prevents the user from manually unhiding it by using the user interface.

You loop back to get the next sheet in Step 6.

In Step 7, after all sheets are evaluated, the macro saves the workbook and ends.

Now, you have to write a macro to handle all the actions that need to happen when the workbook opens:

  1. Activate Visual Basic Editor by pressing ALT+F11.

  2. In the Project window, find your project/workbook name and click the plus sign next to it to see all the sheets.

  3. Click ThisWorkbook.

  4. In the Event drop-down box, select the Open event.

    image3.jpg

  5. Type or paste the following code:

Private Sub Workbook_Open()
'Step 1: Declare your variables
  Dim ws As Worksheet
'Step 2: Start looping through all worksheets
  For Each ws In ThisWorkbook.Worksheets
'Step 3: Unhide All Worksheets
  ws.Visible = xlSheetVisible
'Step 4: Loop to next worksheet
  Next ws
'Step 5: Hide the Start Sheet
  Sheets("START").Visible = xlVeryHidden
End Sub

In Step 1, you declare an object called ws to create a memory container for each worksheet you loop through.

In Step 2, you start the looping, telling Excel that you want to evaluate all worksheets in this workbook.

In Step 3, you unhide the sheet that is currently being looped. This step effectively unhides all worksheets as each sheet is made visible.

You loop back to get the next sheet in Step 4.

After all sheets are made visible, Step 5 hides the START sheet. Again, you use the xlVeryHidden property so that the user will not be able to manually unhide the sheet by using the user interface.

After both macros are implemented, you’ll have a workbook that will work only if the user enables macros!