DLS,

First of all, the Before_Close Event is the wrong event to use for this.
When the workbook is open, all sheets are visible (so you said).
If the user saves it, it's saved with all of those sheets still visible.
He/she then closes the file which runs your code to hide the sheets
but does nothing to the saved copy.
If he/she opens it again without macros enabled, all the sheets
will be visible.

I think what you're looking for is a way to make your workbook
useless unless macros are enabled???
If so, let me offer some code below.
In the code below, sheet1 is used as a warning sheet and should have
some dialog on it telling the user that they have to have macros enabled
for the workbook to operate.

In the Workbook Module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
' Remember where the user was so that we can return there
FromWhatSheet = ActiveSheet.Name
FromWhatCell = ActiveCell.Address
HideSheets ' Sub to hide all but the warning sheet
End Sub
Private Sub Workbook_Open()
' Prevent Sheet1 Activate code from firing
Application.EnableEvents = False
ActivateWorkbook ' Sub to unhide sheets
Application.EnableEvents = True
End Sub

and in a regular module:

Option Explicit
Public cCount As Integer
Public FromWhatSheet As String
Public FromWhatCell As String
' Always insure that at least one sheet is always visible
' or you'll get an error.
Sub HideSheets()
' Insure Sheet(1) is Visible
Sheets(1).Visible = True
' Hide other sheets
Sheets(2).Visible = xlVeryHidden
Sheets(3).Visible = xlVeryHidden
End Sub
Sub ActivateWorkbook()
' Unhide sheets 1 & 2
Sheets(2).Visible = True
Sheets(3).Visible = True
' Hide the warning sheet
Sheets(1).Visible = xlVeryHidden
' If you got here becuase of opening the workbook,
' there is no activecell yet so exit the sub
If FromWhatCell = "" Then Exit Sub
' You got here because of a save. On the save, your
' last activecell was saved to "FromWhatSheet" and
' "FromWhatCell". Take the user back to exactly where
' they left off before the save.
' Activate that cell.
Worksheets(FromWhatSheet).Activate
Range(FromWhatCell).Activate
End Sub

and last but not least (important)
in the sheet1 module:

Private Sub Worksheet_Activate()
' The below line is necessary to allow the Sheet Activate Event
' to take place before calling the ActivateWorkbook Sub.
' Not exactly sure why, but it won't work without the delay.
Application.OnTime Now + TimeValue("00:00:01"), "ActivateWorkbook"
End Sub

Try the above in a separate workbook and then modify it
to suit your needs.

John


DLS wrote:

Hello All, Here's my problem. I have a workbook with 6 spreadsheets. I
want to write coding to hide all but the first worksheet before
closing and to password protect them. I want to hide the password is a
cell on one of the hidden sheets and when Excel asks for the password
to protect the sheets, it will be supplied to Excel invisibily to the
user and then the workbook will close. When the user later opens the
workbook, if he disables the macros on opening, the workbook will have
only one spreadsheet visible. If he enables the macros, then one of
the opening macros called from autoopen will unprotect the hidden
sheets by invisibily supplying the password to Excel and unhide the
sheets for use.
I'm having trouble with the syntax for beforeclose and with supplying
the password automatically. All help will be appreciated.
DLS