File|Close w/Save fires Workbook_Open twice

  • Thread starter Thread starter Mike Preston
  • Start date Start date
M

Mike Preston

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
 
Do you have a

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

Post your BeforeSave code.
 
Do you have a

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


Post your BeforeSave code.
Click to expand...

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
 
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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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
 
Anybody else have any ideas or does my "the file may be partially
corrupted" have some merit?

Thanks

mike
 
I'm still very new to VBA, so I don't have any ready answers. But I have an
idea - and I hope it's valid! What I would do in this case is to code both
the "File|Close (accept save)" and "File|Save (wait for it to finish); then
File|Close" commands into modules, then step through it with the debugger.
Maybe then you could catch the point in the Close or Save procedures at
which it triggers a re-open. Like I said, I'm green, and this may not be too
valid - you sound like you're a better judge of that than me. I just
thought that having it all in code so you could watch what's happening at
every step might give some clues.

Ed

Mike Preston said:
Anybody else have any ideas or does my "the file may be partially
corrupted" have some merit?

Thanks

mike

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
Click to expand...
Click to expand...
 
That would certainly do it.

the workbook closes, but then having fired that event by activating that
sheet, the Excel application is still scheduled to run the activateworkbook
macro. when the time comes, To run it it opens the workbook back up.
Click to expand...

Well, I apologize for not remembering that line was there earlier. By
just removing it, it appears to have solved the problem. But I'm
curious why it would have been needed in the first place.

Thanks again.

mike
 
Back
Top