Macros in Excel : Forcing Users to Enable Macros

Many times we face issue of disable macros situation while the user is using our project. Since the macros are disabled, there is no way that the macros in your project will work the dessired way. However you can force your users to enable macros while working with your files. Simplest way is to hide the main sheet in your project using xlVeryHidden property in your macro. The workseets hidden by using this method can be made visible only by using VBA. The unhide workseet dialog box does not display the names of sheets hidden by using VBA with xlVeryHidden property.



You need to have one sheet which displays message that macros are disabled and needs to be enabled to use this file. Once the macros are enabled, the message sheets can be hidden and data sheets can be made visible. You can copy the following code to Thisworkbook section of your VBA project.

 Private Sub Workbook_BeforeClose(Cancel As Boolean)
   
    Sheets("Msg").Visible = True
    Sheets("Data").Visible = xlVeryHidden

End Sub

Private Sub Workbook_Open()

    Sheets("Data").Visible = True
    Sheets("Msg").Visible = xlVeryHidden

End Sub

First macro code makes Msg Sheet visible and hides Data Sheet. This macros runs on even of closing the workbook. Second macro makes Data Sheet Visible and hides Msg Sheet. This macro runs on even of opening of workbook.

You can add following line to first macro to compulsoraly save the workbook before closure. However use this only if you are sure that it is okay to save the file before closing.

   ActiveWorkbook.Save

In case you have multiple sheets and you want to hide all the sheets except the Msg sheet, use following code in ThisWorkbook Module. Make sure that the Msg sheet is first sheet of your workbook. Following code will keep first sheet visible on close and Unhide all other sheets and hide first sheet on open.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim myCount 'This line of code is optional
Dim i 'This line of code is optional
On Error Resume Next
myCount = Application.Sheets.Count

Sheets(1).Visible = True
Range("A1").Select
For i = 2 To myCount
Sheets(i).Visible = xlVeryHidden
If i = myCount Then
End If
Next i
ActiveWorkbook.Save

End Sub

Private Sub Workbook_Open()

Dim myCount 'This line of code is optional
Dim i 'This line of code is optional
On Error Resume Next

myCount = Application.Sheets.Count
For i = 2 To myCount
Sheets(i).Visible = True
If i = myCount Then
Sheets(1).Visible = xlVeryHidden
End If
Next i

End Sub 

Download file with Macro code to Force users to Enable Macros

Code above is simplest way to force normal users to enable macros and make your project file work the way you want them to. However the advanced users can always get into your project by using VBA routine stored in onother file. This can be done by following the steps as given below.

1. Open the project file while the Macros are disabled.
2. Encable the Macros.
3. Unhide the hidden sheets in project file by macros code in another file

There are ways and means to overcome this situation also, but a advanced user will find a way to get into your project if they realy want to. So I feel there is no need to complecate the code for keeping them away. You can always make normal users to use your project file the way you want them to by using simpe code given above