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.

--
Regards,
Tom Ogilvy



"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

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

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

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

Thanks

mike

On Thu, 28 Aug 2003 14:16:30 GMT, (Mike
Preston) wrote:

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

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

Thanks

mike

On Thu, 28 Aug 2003 14:16:30 GMT, (Mike
Preston) wrote:

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

On Thu, 28 Aug 2003 16:35:45 -0400, "Tom Ogilvy"
wrote:

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.

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




"Mike Preston" wrote in message
...
On Thu, 28 Aug 2003 12:32:27 -0700, "Ed"
wrote:

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.

Hi, Ed, thanks for the response.

I'm not aware of how to put File|Close into VBA. By my way of
thinking it is the user selecting the File Menu and then selecting the
Close option.

If I find an answer I'll let everybody know, just for the record, but
as of now, I'm confused because I have put msgboxes at the beginning
of every single sub and function and it is the darndest thing.

Another potential thought I had is that it is related to a post made
by John Wilson a few months back:


http://www.google.com/groups?hl=en&l...ine.net&rnum=5

Maybe it has something to do with the sub I forgot to mention earlier.
In my worksheet which is to remain unhidden if macros are not enabled
(Sheet1) there is the following sub:

Private Sub Worksheet_Activate()
Application.OnTime Now + TimeValue("00:00:01"), "ActivateWorkbook"
End Sub

The comments from John Wilson's post (which is not the source of my
workbook but it sure does appear that the person I got this from used
John Wilson's approach without telling me where it came from - I found
it in a google search a few minutes ago) say:

' 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"

When people put "Not exactly sure why" into their comments it is a
good bet that that is a good place to start looking for problems.

If anybody has any other thoughts, I would appreciate it.

Thanks

mike