Below is code taken from Daniel Klann's site.
http://www.danielklann.com/excel/for...be_enabled.htm

The idea is the sheets are hidden when you load the workbook and are unhidden (only) by the workbook_open event, so macros must be enabled to use the workbook. The BeforeClose event hides the sheets, but in order to work, forces the user to save. I've tried incorporating the beforesave event to allow the user the option of not saving, but even when I disable events, I get asked twice whether I want to save or not.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
HideSheets
End Sub

Private Sub Workbook_Open()
UnhideSheets
End Sub

Private Sub HideSheets()
Dim sht As Object
Application.ScreenUpdating = False
ThisWorkbook.Sheets("Macros Disabled").Visible = xlSheetVisible 'This sheet contains a message to the user.
For Each sht In ThisWorkbook.Sheets
If sht.Name < "Macros Disabled" Then sht.Visible = xlSheetVeryHidden
Next sht
Application.ScreenUpdating = True
ThisWorkbook.Save
End Sub

Private Sub UnhideSheets()
Dim sht As Object
Application.ScreenUpdating = False
For Each sht In ThisWorkbook.Sheets
sht.Visible = xlSheetVisible
Next sht
ThisWorkbook.Sheets("Macros Disabled").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub