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

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 :-)

Thanks for the info on abreviations.

Adrian gets to be SandyUK because I am based in Sandy England and being
new to news groups etc i wasn't sure i wanted to use my name. I have
since found that its cool to do so and would make life easier as i have
had a couple of people refer to me as Sandy.

Take it easy

Adrian


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

Well I have learnt something, didn't know about Sandy Beds before!

Bob


"SandyUK" wrote in
message ...

Thanks for the info on abreviations.

Adrian gets to be SandyUK because I am based in Sandy England and being
new to news groups etc i wasn't sure i wanted to use my name. I have
since found that its cool to do so and would make life easier as i have
had a couple of people refer to me as Sandy.

Take it easy

Adrian


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

Cool, I aim to learn something new each day but at the moment its about
50 things (helped by the fact that i am spending an hour a day reading
Proffesional Excel Development which i highly recomend).

Where are you based Bob?

Adrian


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

Me. I'm in Sunny Poole on the South Coast. Not as close as Norman, but not
far away.

Bob

"SandyUK" wrote in
message ...

Cool, I aim to learn something new each day but at the moment its about
50 things (helped by the fact that i am spending an hour a day reading
Proffesional Excel Development which i highly recomend).

Where are you based Bob?

Adrian


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

Who's Norman

I spent a year at Uni in Bournemouth so you can't pull the wool over m
eyes its nice when its sunny but from what i remember its not sunny tha
often <g

Do you use Excel / write VBA for a living? If so is there a living t
be made out of it?

Regards

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

Norman is Norman Jones. I thought that he had answered one of your questions
and gave away his location, St Albans, but looking back it was another
response.

Poole is not Bournemouth, never was, never will be. Once upon a time we were
even in separate counties, but Bournemouth got lonely and snuggled over to
Dorset. But we don't talk about them. Poole is lovely, never rains, always
sunny, ...

No, I do not make a living out of Excel/VBA, although I do use it
peripherally at work. There is a living to be made from it, there are people
doing it, but I do not believe that it is a lucrative market.

Regards

Bob


"SandyUK" wrote in
message ...

Who's Norman

I spent a year at Uni in Bournemouth so you can't pull the wool over my
eyes its nice when its sunny but from what i remember its not sunny that
often <g

Do you use Excel / write VBA for a living? If so is there a living to
be made out of it?

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 & Sandy!!

I have just read this thread - quite amusing in parts. I have faced many
similar problems (RE cutting your leg off rather than accepting something
new), my most tedious was an office where we had to print 3 different
coloured copies of each letter. But rather than using all three trays of
the printer they made us change the paper for each colour!!!

I would like to make a suggestion and apologise if Bob already suggested
something similar that I mis-understood (my solution seems far too obvious)

My suggestion would be to have two documents:-

1) The quotes excel sheet - with an additional version control sheet
detailing the revision history.
2) A version control sheet

Let say you have issued V1.0 of the quotes sheet and you issue a new version
(V1.1). The quote sheet then checks the version from its revision history
against the version control sheet and produces an error.

Hmmmmmm just given it a bit more thought - I suppose the problem is the
existing sheets dotted around on peoples desktops isn't it?

How many users are you talking about? If you have got a smallish number it
may be possible to determine that although you have 50 users there are only
10 different versions of the spreadsheet in population. These 10 code I
mentioned earlier could have a list of file's that couldn't be openned as
well (described by filename, size and location).

I think you have 3 options

1) Cut off your own leg
2) Distribute a modified version of the existing sheet that links to a
version control sheet managed by yourself
3) Start with a completely new solution

I understand your difficulties with getting technophobes to accept new
technology - so your problem is getting the staff to use anything new you
give them - Ironically I think that would be easier with a completely new
solution as although you would have to face the flack for a bit, at least
people would be forced to use the new system.

Sorry for not being brief.

Andy




"Bob Phillips" wrote in message
...
Norman is Norman Jones. I thought that he had answered one of your
questions
and gave away his location, St Albans, but looking back it was another
response.

Poole is not Bournemouth, never was, never will be. Once upon a time we
were
even in separate counties, but Bournemouth got lonely and snuggled over to
Dorset. But we don't talk about them. Poole is lovely, never rains, always
sunny, ...

No, I do not make a living out of Excel/VBA, although I do use it
peripherally at work. There is a living to be made from it, there are
people
doing it, but I do not believe that it is a lucrative market.

Regards

Bob


"SandyUK" wrote in
message ...

Who's Norman

I spent a year at Uni in Bournemouth so you can't pull the wool over my
eyes its nice when its sunny but from what i remember its not sunny that
often <g

Do you use Excel / write VBA for a living? If so is there a living to
be made out of it?

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