Apparently not possible?

[Guest]

Office 2003 and Win XP;

I have a normal XLS file which runs hidden (Window-Hide). This file is run
from a toolbar. This hidden file does things to other files from the toolbar
and in the process some data gets written into the hidden file, but it is
only needed temporarily.

The hidden file is shared, so it is set to open read only by default using
file properties. Apparently, it is not possible to code this file so when a
user closes MS-Excel the hidden file will close itself without warnings,
without messages, and without saving? i.e. just drop dead, quietly?

I have tried all manner of the following in either and both the Before_Close
and Before_Save events:

Cancel = True
Application.EnableEvents = False
Application.DisplayAlerts = False
ThisWorkbook.Saved = True
ThisWorkbook.Close SaveChanges:= False

No matter what I try, I ALWAYS get the save file question. If not
impossible, please tell me how it can be done.

Thanks much in advance from a very frustrated user.

 

[Guest]

I assume the workbook is being closed manually, since you are trying to use
the before close and before save methods to indicate the file is saved to
reject changes.
Otherwise, the ThisWorkbook.Saved = True would work if entered just before
the close command in code. For some reason, it won't work in the before
close/save execution. You might be able to work around it by putting a close
button on the toolbar and close the file by code each time. Then it should
work.

 

[Guest]

Also, if being closed by code, have you tried this syntax?:

Workbooks("myFile.XLS").Close SaveChanges:=False

 

[Bill Renaud]

I think you have too many lines of code in your event handler, and they are
canceling out the close operation, so the hidden workbook doesn't actually
close. Try the following:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub

 

[Guest]

Thanks for the effort,

Yes, the file would be closed by the user, so having a close button on the
toolbar would not work, since the users could circumvent this by closing
normally, and they will. Even if instructed not to, they will, due to memory
or whatever.

I also tried the alternate close: Workbooks("myFile.XLS").Close
SaveChanges:=False

Nothing works. Any other ideas?

 

[Guest]

Thanks, Bill.

That was one of the very first things I did try; it fails, I still get the
message box...

Any other ideas?

 

[Bill Renaud]

Strange. I set this up as a Read-Only file (using Explorer) on Windows ME
with Excel 2000 SP-3, and it works fine on my machine. I can open the file,
unhide the workbook, make a change, hide the workbook, then exit Excel
without getting any prompts.

What version of Excel are you running?

 

[Bill Renaud]

Another idea:

If your shared, hidden workbook is really Read-Only, then maybe you should
just use it that way. Create another temporary workbook to save temporary
data and close that one when any macro is finished running. Then your
hidden workbook will never have any changes to it, so there will be no
prompts.

 

[Guest]

Hi Bill,

Oddly enough, I tried the same thing you did, only at home. I just set up a
read only hidden file, then I wrote to it from another file using code, to
change its contents. It worked just fine. At home I am running Office 2003
with Win XP, just like at work.

I'm going to try this test tomorrow at work and see if it works. If it does,
then something else is at play that I'm not accounting for.

Thanks for your help; I may post back again depending upon what I find...