mandatory worksheet closing

  • Thread starter Thread starter sunroyal
  • Start date Start date
S

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
 
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
 
John,

I would like it to close after they deactivate the workbook by togglin
off, is that do-able
 
sunroyal said:
I would like it to close after they deactivate the workbook by toggling
off, is that do-able?
Click to expand...

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'?
 
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?
 
sunroyal said:
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?
Click to expand...
....

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.
 
The only problem with that is the source file is in a different drive o
the network. Does that matter
 
sunroyal said:
The only problem with that is the source file is in a different drive on
the network. Does that matter?
Click to expand...

No, as long as the external reference links in the formula use the full
pathname for the other workbook.
 
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
 
Back
Top