On Thu, 28 Aug 2003 09:49:54 -0400, "Tom Ogilvy"
wrote:

Certainly nothing in the minimal code you show that would cause the behavior
you describe - but since this appears to be some type of protection scheme,
I suspect you have a before close event as well.

Nope. In the ThisWorkbook module I have two subs:

Workbook_Open
Workbook_BeforeSave

In the regular module I have 2 subs

HideSheets()
ActivateWorkbook()

Hidesheets is below, ActivateWorkbook just does the opposite of
HideSheets:

Sub ActivateWorkbook()
' Unhide sheets
Sheets("Sheet2").Visible = True
Sheets("Sheet3").Visible = True
Sheets("Sheet4").Visible = True
Sheets("Sheet5").Visible = True
' Hide the warning sheet
Sheets("Sheet1").Visible = xlVeryHidden
' If you got here because 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

You need to walk through all your event code and see if you have anything
that would cause the behavior described.

I have. And I can't find anything. :-(

You might want to put msgboxes in all your events and then see what is
firing and when. You could be getting some type of recusive calling going
on.

Been there. Done that. That is what tells me that the Workbook_Open
event is firing twice. The strange thing is that I get different
behaviors between:

1) File|Close (accept save)

and

2) File|Save (wait for it to finish); then File|Close

The second one works just fine.

The first one appears to close the file and then re-open it.

The other methods of closing work just swell:

1) File|Close with no changes made to spreadsheet (no save question
arises)
2) File|SaveAs (save to new name), File|close

As you mentioned, this is a protection scheme, so if a user opens the
workbook without enabling macros, the intent is to have only one
worksheet visible, with a message that tells the user to close down
the workbook and reopen it, this time enabling macros.

When the workbook is opened with macros disabled, it also works as
expected. That is, only one worksheet is visible and the other "real"
worksheets are as hidden as I can make them (xlSheetVeryHidden).
And, when the one worksheet is slightly changed (just moving the
selected cell) and then exited with [File|Close (accept save)], it
exits as expected (the Workbook_Open sub does not fire twice).

I'm beginning to think I need to copy the sheets, one by one, to
another workbook as maybe this one is slightly corrupted.

Thanks again.

mike

"Mike Preston" wrote in message
...
On Thu, 28 Aug 2003 09:11:51 -0400, "Tom Ogilvy"
wrote:

Do you have a

ThisWorkbook.Close SaveChanges:=True
in your BeforeSave code?

No.


Post your BeforeSave code.

From the ThisWorkbook module:

FromWhatSheet = ActiveSheet.Name
FromWhatCell = ActiveCell.Address
HideSheets ' Sub to hide all but the warning sheet

From a standard module:

Sub HideSheets()
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = xlSheetVeryHidden
Sheets("Sheet3").Visible = xlSheetVeryHidden
Sheets("Sheet4").Visible = xlSheetVeryHidden
Sheets("Sheet5").Visible = xlSheetVeryHidden
End Sub

Thanks for the quick response, Tom.

mike

"Mike Preston" wrote in message
...
Excel 97.

I have a small module in Workbook_Open() in the ThisWorkbook area.
Otherwise there are maybe 100 lines of code spread between 1 worksheet
module and a regular module. When I save the Excel workbook by
File|Close, and then answer YES, the code that I have in
Workbook_BeforeSave is executed, the file is saved and then it
re-opens and executes Workbook_Open. I have a simple msgbox prompt in
the Workbook_Open sub and it fires when I open the workbook and then
again when I close it with File|Close. If I do a File|Save and then
close Excel, everything happens as it should.

Has anybody run into this before? Can I provide any more information
to help you tell me what I might be doing wrong?

Thanks

mike