Opening another workbook

[Emma Hope]

I have a spreadsheet (A) that links to another spreadsheet (B), if i open
just (A) then all my linked cells are #Value errors, so i need to open (B) as
well.

Problem is, its actually my users who will be doing this and unfortunately
this task is a bit difficult for them (don't ask!!!!)

So i have written some code in the Workbook_Open() event of (A) to open (B).
Then they see another spreadsheet is open and they try to close it -
problem!!!

So i want to open (B) and then hide it in some way so it doesn't show on
their toolbars, in the window etc, but i can't find the code to do this.

The only other problem here is that some users use spreadsheet (B) for other
purposes and therefore may already have it open, therefore i would like some
sort of STOP i.e. a message that says, you already have (B) open, you cannot
continue until you have saved and closed (B) which then stops the macro and
closes (A) as well.

Thanks for the help.
Emma

 

[Jim Thomlinson]

Personally I avoid linking workbooks together as I find it cuases more
problems than it solves. That being said I would caustion you against hiding
workbook B once it is opened. If your user has it open and does not know that
they have it open then they are unaware of what they are up to. If something
goes wrong and they successfully close A without B closing then they are
holding B open and will have no idea that that is the case. There is just too
much room for things to go wrong here. I would recomend sitting your users
down for a quick lesson in XL and showing then how things work. They will be
better informed and more able to make good decisions if and or when things go
wrong. And believe me at some point (no matter how well thought out your
process) things will go wrong. The moment you devise a fool proof system the
world devises a better fool to mess things up.

 

[Emma Hope]

To Jim and any other answerers, please don't take offence because it isn't
intended but i have raised several questions lately, the answers have been
along the lines of 'i wouldn't do it like that'.

I understand why this is the answer on some occasions but sometimes you have
no choice but to do it a certain way and i would still like some advice on
how to do it.

Alternatively, if i'm not doing it in the best way, a suggestion of how to
do it better would be useful. Unfortunately the suggestion of 'teach your
users' is only helpful in very limited circumstances, my users are spread out
around the country and i have neither the time or resources to get round them
all and to be honest some of them would take days of work to get them up to
speed, when i want is for them to be able to open a spreadsheet and look at
some numbers.

Further to my specific question:

I understand that if the user closes A and B fails to close then it will be
kept open and might cause problems but its a risk i'm willing to take knowing
my users as i do.

Is anyone aware of a way to answer my original question? I'm sure its
relatively easy for an experienced VBA user. Or suggest a alternative that
will work (it must be 'automated', i.e. nothing for the end user to do!) in
this situation.