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

If you don't save, then the sheets won't be hidden when the workbook is next
open which defeats the whole approach. So using it, while a waste of time
before, becomes an even more complete waste of time now.

--
Regards,
Tom Ogilvy

"Rob" wrote in message
...
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

Thanks for your response Tom. If macros are preventing user 'breaking' the workbook then it's not a waste of time to force the user to enable them.
The fact that if you don't save then sheets won't be hidden when the workbook is next open was precisely my point. I'd like to remove the line where the book is saved from the hidesheets sub, and then use the beforesave event similarly to below. By doing so, any SAVED version of the workbook will have the sheets hidden. The problem I'm having is that the Save changes dialog appears twice, and I can't see why.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
cancel=true
application.enableevents=false
hidesheets
thisworkbook.save
unhidesheets
application.enableevents=true
end sub


----- Tom Ogilvy wrote: -----

If you don't save, then the sheets won't be hidden when the workbook is next
open which defeats the whole approach. So using it, while a waste of time
before, becomes an even more complete waste of time now.

--
Regards,
Tom Ogilvy

"Rob" wrote in message
...
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

I only get two prompts if I choose to save in response to the first to the
first prompt - same for you?

Close the workbook
BeforeClose
HideSheets
Executes a Save
Fires BeforeSave
Events off,
Save Canceled
Hide Sheets
Executes a Save
File is Saved
Back to BeforeSave
File is Saved
Unhide Sheets
Events back on
Back to BeforeClose
BeforeClose Ends
Workbook is Dirty prompt for Save, User clicks yes
BeforeSave
Events Off
Save Cancelled
Hide Sheets
Executes a Save
File is Saved
Back to BeforeSave
File is Saved
UnHide Sheets
Events Back on
Excel must recheck if the workbook has changed and issue another prompt.
If I answer no, 4 saves later and I am done.

--
Regards,
Tom Ogilvy

"Rob" wrote in message
...
Thanks for your response Tom. If macros are preventing user 'breaking'
the workbook then it's not a waste of time to force the user to enable them.
The fact that if you don't save then sheets won't be hidden when the
workbook is next open was precisely my point. I'd like to remove the line
where the book is saved from the hidesheets sub, and then use the beforesave
event similarly to below. By doing so, any SAVED version of the workbook
will have the sheets hidden. The problem I'm having is that the Save
changes dialog appears twice, and I can't see why.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
cancel=true
application.enableevents=false
hidesheets
thisworkbook.save
unhidesheets
application.enableevents=true
end sub


----- Tom Ogilvy wrote: -----

If you don't save, then the sheets won't be hidden when the workbook
is next
open which defeats the whole approach. So using it, while a waste of
time
before, becomes an even more complete waste of time now.

--
Regards,
Tom Ogilvy

"Rob" wrote in message
...
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