Hyperlinks - Opened files are disappearing from menu->window

[basys]

My apologies if this has been posted before,
the search page will not allow me to view beyond page 1 of results,
(it returns a PHP error).

In Excel 97 SR2.
Already opened files are disappearing from the menu->window openfiles listing when navigating between files using hyperlinks.

Has anyone else noticed this ?
Any suggestions how to prevent it ?

TIA

 

[Chris Davison]

this may be due to there being no changes being made to the spreadhseet before the hyperlinking is used (ie clicked on)

try putting a volatile function in a spare cell ( something like =now() which will always return the current time)

since the time will update itself, or require a save it might get rid of the auto-closing

there's probably some other better VBA solutions too

 

[Mudface]

You should also be able to get back to the files by using Alt and the left or right cursor key.

 

[basys]

Hi Folks.
Thanks for the replies.

Chris -
The files in question have not been closed,
rather they just disappear from the list.
Hopping between two sheets via hyperlinks,
only the currently visible sheet
is displayed in the window list.


Mudface -
Your tip works nicely,
I should have RTFM :wink:

Many thanks.

 

[basys]

P.S.

I'd like to find a soln. for this problem
as the sheets (possibly > 1000)
are destined for use by techno-numpties :wink:

The sheets are a front end info collators
for various databases
and are read-only for end users.

Some sheets are quite large,
and take > 5 min to update
so all are set with auto-recalculation off.
I don't want to frighten end users who may think their machines have locked up.

Digression -
I use now() as a quick and dirty method
to generate a notification of
when some sheets were last updated
from the master databases.

Refreshing the sheets via F9
causes an newer than true update time to be displayed.

I intend to create a macro which
will run the database updates,
refresh the sheets,
and update a cell containing a true value
of when the sheet info was genuinely updated.

Any pointers gratefully accepted :wink:

TIA
Paul