mandatory worksheet closing

[sunroyal]

I there a way to make users close a workbook after viewing or having i
close automatically so any updates would not be missed

 

[John Wilson]

sunroyal,

Closing the workbook is easy.
ThisWorkbook.Close
will close the workbook.
Application.Quit
will close out of Excel
(Note: put
Application.DisplayAlerts = False
before either of those lines of code to suppress any warnings)

Now the problem is...what event do you want to use to call the code???
Once the user opens the workbook and is "viewing" it, what action
on their part would you want to use to close it down? Selecting a cell,
selecting another sheet, calculating, etc.???

For a list of events and explanations, take a look here:
http://www.cpearson.com/excel/events.htm

John

 

[sunroyal]

John,

I would like it to close after they deactivate the workbook by togglin
off, is that do-able

 

[Harlan Grove]

I would like it to close after they deactivate the workbook by toggling
off, is that do-able?

This may not work as reliably as you'd like. If a user uses [Alt]+[Tab] to
switch applications, this triggers no event in Excel. They could also launch
a separate instance of Excel as a separate application, then switch back and
forth between Excel instances, thus gaining almost the same functionality as
switching back and forth between workbooks in the same Excel instance.

So the more pertinent question may be why are you trying to do this? That,
or what do you mean by 'toggling off'?

 

[sunroyal]

Basically, I built a productivity workbook that accesses information
around our network. I work for an institutional lender that releases a
pricing sheet that periodically changes.

I built a custom toolbar that contains a macro that accesses a
duplicate pricing sheet that is linked cell by cell to the workbook
that changes, so that macros are allowed to hyperlink via buttons on
the toolbar.

Therefore, when a new sheet is released, agents will open the new
pricing sheet and their productivity workbook will update. Having the
new sheet close itself will insure that they will not leave it open and
miss further updates. Make sense?

 

[Harlan Grove]

Therefore, when a new sheet is released, agents will open the new
pricing sheet and their productivity workbook will update. Having the
new sheet close itself will insure that they will not leave it open and
miss further updates. Make sense?

....

So your users have the productivity workbook open most or all of the time,
but periodically update the pricing worksheet in it? And this is done by
opening a separate file that feeds values into the pricing worksheet? If so,
all you should need to do is have your toolbar buttons run macros containing
the statement

ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources

Then you wouldn't need to open the file that contains the periodically
updated pricing data, just pull values from it. Granted to do this Excel
does open the file and read from it, but it closes the file automatically
once it has read what it needs.

 

[sunroyal]

The only problem with that is the source file is in a different drive o
the network. Does that matter

 

[Harlan Grove]

The only problem with that is the source file is in a different drive on
the network. Does that matter?

No, as long as the external reference links in the formula use the full
pathname for the other workbook.

 

[sunroyal]

Do I place that formula in a module?

Example:

c/windows/desktop/smartguide=j/smartguidereference/currentpricing/correspondent


This would be smartguide(productivity sheet) and smartguide referenc
folder containing the current pricing folder with the corresponden
pricing sheet.

Would this be correct