Force Users to enable macros

[Nghia Dao]

Hi all,

I am new to the forum. Nice to meet all of you and hope you can assist me in the following macros.

I am building a macro to force users to enable macros before they can use it. I googled and found the following codes. The purpose is hiding all sheets except the sheet name "Enable_Macros" and unhide them if users already enabled macros.

The code works normally if I open only one workbook. However, if I open two and more workbooks, when I click X button to "Close" the workbook and choose save, the save message box appears again and again. Please help me how to fix the indefinite saving message box like that. I am using Excel 2016. I also attached the sample workbook.

Thank you very much for your supports!

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

Dim ws As Worksheet, wsSplash As Worksheet
Dim actws As String
Dim cell As String
Application.ScreenUpdating = False
Application.EnableEvents = False
Set wsSplash = Worksheets("Enable_Macros")
cell = ActiveCell.Address
actws = ActiveSheet.Name
wsSplash.Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Enable_Macros" Then ws.Visible = xlSheetVeryHidden
Next ws
Cancel = True
ActiveWorkbook.Save
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Enable_Macros" Then ws.Visible = xlSheetVisible
Next ws
wsSplash.Visible = xlSheetVeryHidden
Sheets(actws).Select
Range(cell).Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub


Private Sub Workbook_Open()
Dim ws As Worksheet, wsSplash As Worksheet

Application.ScreenUpdating = False
Set wsSplash = Worksheets("Enable_Macros")
wsSplash.Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Enable_Macros" Then
      ws.Visible = xlSheetVisible

  End If
Next ws
wsSplash.Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub

 

[AlanSidman]

Look at this video.

http://www.datapigtechnologies.com/flashfiles/forcemacros.html

 

[Nghia Dao]

Look at this video.

http://www.datapigtechnologies.com/flashfiles/forcemacros.html

Thanks AlanSidman for your help. I have tried using the BeforeClose event as similar as your video but I faced a problem if user click "Cancel" after click Close. Then case study sheet was hidden and the workbook was still opening. It made user get stuck there.

Any solutions for this?

 

[Artik]

Nghia Dao, Do not use the _BeforeClose event. Try at the end of both procedures (_BeforeSave and _Open) add:

Me.Saved = True

Artik