Version Control

[SandyUK]

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

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[SandyUK]

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

[Bob Phillips]

"SandyUK" <[email protected]> wrote in
message news:[email protected]...
>
> 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!

[SandyUK]

Hi Bob

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.

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

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.

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

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

All the best

Adrian

[Bob Phillips]

"SandyUK" <[email protected]> wrote in
message news:[email protected]...

> 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.

[SandyUK]

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

[Bob Phillips]

"SandyUK" <[email protected]> wrote in
message news:[email protected]...

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

[SandyUK]

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

[Bob Phillips]

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

Bob


"SandyUK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[SandyUK]

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

[Bob Phillips]

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

Bob

"SandyUK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[SandyUK]

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

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Andibevan]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>