I would simplifiy it this way, including a safety line to keep Sheet1 visible.

Private Sub CloseHideSheets()
Dim x as integer
For x = 2 to sheets.count
Sheets(x).visible = false
Sheets("Sheet1").visible = True
Next x
End Sub
--
Best wishes,

Jim


"JustBreathe" wrote:

Thanks Vergel. This is what I've gone with, and it works...with one qwirk.
If the workbook was saved just prior to opting to close it, then the workbook
instantly closes out upon selecting to close without giving the vbYesNoCancel
selection and without running the BeforeClose procedure that hides all
worksheets except Sheet1. Can you eye what I might be missing here?! Here
is my code...

tia!!!
Tanya (teaching myself VBA as I go here)


Private Sub CloseHideSheets()
Dim Wks As Worksheet
Set Wks = Worksheets("Sheet1")
Wks.Visible = xlSheetVisible
For Each Wks In ThisWorkbook.Worksheets
If Wks.Name < "Sheet1" Then
If Wks.Visible = xlSheetVisible Then Wks.Visible = xlSheetHidden
End If
Next Wks
End Sub
_____________________________________
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
'This code runs the CloseHideSheets procedure before the workbook is closed.
'This is to make all worksheets hidden, except for Sheet1
'which contains the warning that macros need to be enabled
'at the time the workbook is opened in order to work in it.
'The Workbook_Open() sub will unhide all other sheets upon
'opening if macros are enabled.
'
Dim Msg As String
If Me.Saved = False Then
Msg = "Do you want to save the changes you made to "
Msg = Msg & Me.Name & "?"
Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
Call CloseHideSheets
Me.Save
Case vbCancel
Cancel = True
Exit Sub
End Select
End If
Call CloseHideSheets
Me.Saved = True
End Sub





"Vergel Adriano" wrote:

Hi Tanya,

Maybe something like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If vbNo = MsgBox("Close this workbook?", vbYesNo) Then
Cancel = True
Else
'Put the code here that hides everything except Sheet1
End If
End Sub



--
Hope that helps.

Vergel Adriano


"JustBreathe" wrote:

I've put in the coding that has been suggested to others as the work-around
for not being able to enable macros with a macro.

It hides all the worksheets (except for 'Sheet1' which contains the message
to that the workbook need to be opened with macros enabled ) before closing.
When the workbook is opened with macos enabled, code will unhide those hidden
sheets and hid 'Sheet1'.

This works, except that I have realized that if 'Cancel' is selected when
closing, it leaves the 'Sheet1' reminder sheet visible and the working tabs
hidden. Is there a way to code the beforeclose section so that the worksheet
will revert back to 'sheet1 hidden' and all other sheets visible in the event
the user decides not to close but to continue working in the file?

TIA
Tanya (self-teach VBA as i go)