Adrian,

One asks to ask, if you only want them to use the latest version, why not
just delete the older versions?

Another approach is to put your code in an add-in, store it on a shared
drive, and then they can only use the latest.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SandyUK" wrote in
message ...

I am trying to ensure that all the users of a workbook are all using the
same version as there are 10+ versions out there which look similar and
do similar things but slightly different results and no joy asking
people to just use the new version.

I was thinking that something along the lines of a bit of code that
compares a version number in the code with a number in a specific cell
of a workbook on a shared drive and if they dont match a message box
saying to use the current version then close the workbook.

Can anybody point me in the right direction?

Regards

Adrian


--
SandyUK
------------------------------------------------------------------------
SandyUK's Profile:
http://www.excelforum.com/member.php...o&userid=17487
View this thread: http://www.excelforum.com/showthread...hreadid=390860

Hi Bob

These workbooks were created by a predecessor and have been in use for
7 years and unfortunatly people have saved them all over the place.
They calculate data which is then manually entered all over the place
etc etc. I guess what i am saying is that I can't get to all the old
versions to delete them and because they still work and people being
people just have shortcuts etc they are not keen to delete them or see
any point in upgrading (to them its a matter of a couple of pence per
product which is not a big deal but in the overall scheme of things its
getting towards the £1m mark).

My company has issues with Add ins and at the moment are upgrading to
2003 (slowly) so even more problems keeping the add in available to all
the machines (again easy enough to do but i am dealing with some users
(across multiple locations that i can't get to) who would rather cut
there own foot off than do anything "techie" then shout when they
couldn't walk any more because they appear to be missing a foot).

Suffice to say i am between a rock and a hard place. I need a self
contained workbook which on start up will look to see if it the latest
version and then display a message saying it isn't (if it isn't) but
"if you go to X or call Y" they can get the latest version then close
down (i know i will have to force people to enable macro's but i have
that covered at the momet)

Thanks for the reply, i am hoping yourself or someone out there has
come across a similar situation and might be able to give me a few
pointers.

All the best

Adrian


--
SandyUK
------------------------------------------------------------------------
SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487
View this thread: http://www.excelforum.com/showthread...hreadid=390860

"SandyUK" wrote in
message ...

Hi Bob

These workbooks were created by a predecessor and have been in use for
7 years and unfortunatly people have saved them all over the place.
They calculate data which is then manually entered all over the place
etc etc. I guess what i am saying is that I can't get to all the old
versions to delete them and because they still work and people being
people just have shortcuts etc they are not keen to delete them or see
any point in upgrading (to them its a matter of a couple of pence per
product which is not a big deal but in the overall scheme of things its
getting towards the £1m mark).

It would seem to me that if it is getting twiards the £1M mark, you have an
irrefutabel business case.

My company has issues with Add ins and at the moment are upgrading to
2003 (slowly) so even more problems keeping the add in available to all
the machines (again easy enough to do but i am dealing with some users
(across multiple locations that i can't get to) who would rather cut
there own foot off than do anything "techie" then shout when they
couldn't walk any more because they appear to be missing a foot).

Whjy would they have issues with add-ins, but not VBA in the workbook. The
former makes more sense than the latter, and is more controllable.

Suffice to say i am between a rock and a hard place. I need a self
contained workbook which on start up will look to see if it the latest
version and then display a message saying it isn't (if it isn't) but
"if you go to X or call Y" they can get the latest version then close
down (i know i will have to force people to enable macro's but i have
that covered at the momet)

Do you mean separate from the multi-version workbook? What I am missing at
the moment is how you will be able to amend all of these workbooks when you
say you can't get to them all. If they were all named the same, you could
install a new addin on each machine (same old problem her though) which has
application events that monitor files being opened, if our offending
workbook opens, you could dump the code and automatically load the
up-to-date code. A dictator application if ever I heard one!

Hi Bob

Thanks for the reply I have had a couple of days off (shift work) s
couldn't get back to you straight away. I agree with all you are sayin
Add Ins V VBA in the background the answer is that they don't realis
what the VBA is doing in the background whilst the Add Ins are visabl
there so scare them.

I have been thinking about what you have replyed and so thought i woul
ask advice of yourself and anybody who has been following the posts.

I have inherited multiple versions of a costing workbook that cost
products and which i need to update periodically however, the previou
quotes that have been produced have to remain valid so i cannot jus
link the workbook to a list of variables that make up the quote an
update these as and when.

What i would like to do is produce a stand alone workbook that woul
produce quotes but when i need to change the variables new quotes coul
not be produced using this old workbook. The user would be alerted tha
they can no longer use this workbook and that they need a new quot
template (I would love to just have a template which the users links t
and i update, but i am not aware of a way of stopping a user saving thi
template to a local drive etc. and hence have an old version floatin
around that they can use to get a quote whilst still being able to sav
quotes).

Re reading this not sure it is going to make a lot of sense but though
it would be rude not to reply especially as you had taken the time t
try and help. If you can see what I am driving at and know of an
articles i could read or have advice that would be appreciated.

All the best

Adria

--
SandyU
-----------------------------------------------------------------------
SandyUK's Profile: http://www.excelforum.com/member.php...fo&userid=1748
View this thread: http://www.excelforum.com/showthread.php?threadid=39086

"SandyUK" wrote in
message ...

Thanks for the reply I have had a couple of days off (shift work) so
couldn't get back to you straight away. I agree with all you are saying
Add Ins V VBA in the background the answer is that they don't realise
what the VBA is doing in the background whilst the Add Ins are visably
there so scare them.

Not impressed! Letting work get in the way of the NGs vbg.

I have inherited multiple versions of a costing workbook that costs
products and which i need to update periodically however, the previous
quotes that have been produced have to remain valid so i cannot just
link the workbook to a list of variables that make up the quote and
update these as and when.

Your idea of a template sounds good in this situatrion.

What i would like to do is produce a stand alone workbook that would
produce quotes but when i need to change the variables new quotes could
not be produced using this old workbook. The user would be alerted that
they can no longer use this workbook and that they need a new quote
template (I would love to just have a template which the users links to
and i update, but i am not aware of a way of stopping a user saving this
template to a local drive etc. and hence have an old version floating
around that they can use to get a quote whilst still being able to save
quotes).

How about this for an approach? Have some code in the template that just
loads the master template, and clears that one. This way, you control what
gets loaded. You still have a problem with any existing templates out there
though.Unfortunately, you have this problem because you didn't anticipate
that it could happen (not a criticism, just an observation), else you could
have separated data, presentation and application, and not worried about the
presentation layer.

Thanks for all the advice Bob

I am going to give the whole situation some consideration and may have
had a bit of a brainwave this morning.

I think i am going to change the entire template layout and just reap
the whirlwind of abuse that this will generate from the users (they
will get over it) so that i can start from scratch with an obvious this
is old and this is new situation. I will expire the version after X
months but still make it available in read only mode, unload the copy
command so they can't create a copy of the worksheet and keep using it
(i have an incling of how to do it and know its possible but knowing
something is possible and actually doing it is 2 different things :-)
). I a am also going to put a version number in the code possibly for
later use if i figure out how to use it effectivly.

I am relativly new to forums and don't know what

"Not impressed! Letting work get in the way of the NGs vbg." means?

Could you enlighten me?

All the best

Adrian


--
SandyUK
------------------------------------------------------------------------
SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487
View this thread: http://www.excelforum.com/showthread...hreadid=390860

"SandyUK" wrote in
message ...

I think i am going to change the entire template layout and just reap
the whirlwind of abuse that this will generate from the users (they
will get over it) so that i can start from scratch with an obvious this
is old and this is new situation. I will expire the version after X
months but still make it available in read only mode, unload the copy
command so they can't create a copy of the worksheet and keep using it
(i have an incling of how to do it and know its possible but knowing
something is possible and actually doing it is 2 different things :-)
). I a am also going to put a version number in the code possibly for
later use if i figure out how to use it effectivly.

I think that is what I would so also. You are a victim of the situation, and
you either stick with it and endure the problems, which you know in your
heart is the wrong course, or you take the initial pain for the long-term
gain. And with £1M savings as a target, it has to be worth it doesn't it?

I am relativly new to forums and don't know what

"Not impressed! Letting work get in the way of the NGs vbg." means?

My bit of levity after you said that you had had a couple of days off (of
the Newsgroups) due to shift work. <vbg means very big grin, trying to
convey body language in text. Other forms are <g. <ebg extra big grin, and
:-) smiley.

BTW how does Adrian get to be SandyUK. I nearly called you Sandy at one
stage :-)