AndyPandy27
Board Regular
- Joined
- Jul 17, 2012
- Messages
- 142
Hi All,
I'm working on a new Pricing Tool to be used by a large number of Sales Reps. It will be distributed via email for now (whilst we await the creation of a proper dedicated SharePoint site).
One of the key things we need to keep on top of (given that we are having to distribute this via email) is version control.
My plan is the following:
- In the Tool workbook, have 3 cell references within a hidden worksheet called "Data" (Data!A1, Data!A2 and Data!A3)
- Data!A1 = Hard-Coded version of this particular version of the Tool, e.g. "Version 1.1"
- Data!A2 = Link to a separate Workbook, stored on SkyDrive, which contains the most recent version of the Tool, which I will keep updated as I update the Tool, e.g. "Version 1.2"
- Data!A3 = Formula: IF(Data!A1 <> Data!A2,"No","Yes")
Then I will have some code in the Workbook_Open event of the Pricing Tool, which updates the linked reference in Data!A2, and then checks to see if Data!A3 = "Yes" or "No".
If Data!A3 = "No", then a msgbox will appear explaining that the Tool is out-of-date, and they need to use the latest version. If Data!A3 = "Yes" - nothing happens, and the Pricing Tool opens as normal.
I have the Skydrive workbook saved here: https://d.docs.live.net/f7cb40d31b9852bd/Public/Pricing Analysis Tools Current Versions.xlsm
The most up-to-date version information is contained within Cell C7.
However, I'm running into two major issues with my code:
1. When I open the Pricing Tool, I get the "External Links" warning message, asking to update or ignore - which I don't want users to have to see (I want them to always update).
2. The VBA doesn't seem to allow me to automatically access the Skydrive file, I need to enter a username and password. Again, I don't want users to have to go through that - it should be a relatively quick and automatic process. Either they see a pop-up warning to confirm they need to update, or they see nothing, and they use the Tool.
If anyone can help me to resolve the issues with my current idea, or provide a different solution which would work - I would be most grateful.
nb - the only reason I am using SkyDrive is because it is one of the only "publicly" available hosting sites I could think of, which should mean that the cell could be updated regardless of how/when the user is opening the Tool (provided, of course, they have internet access). I can't host on our company's internal network, as, when working out of the office (as our Sales Reps do), they would have to actively log-in to the network, which, for us, is very troublesome and slow - so more often than not they won't do it. So SkyDrive seemed like the most "bullet-proof" method of ensuring that they could access the external link containing the most up-to-date version information.
Many thanks,
AP
I'm working on a new Pricing Tool to be used by a large number of Sales Reps. It will be distributed via email for now (whilst we await the creation of a proper dedicated SharePoint site).
One of the key things we need to keep on top of (given that we are having to distribute this via email) is version control.
My plan is the following:
- In the Tool workbook, have 3 cell references within a hidden worksheet called "Data" (Data!A1, Data!A2 and Data!A3)
- Data!A1 = Hard-Coded version of this particular version of the Tool, e.g. "Version 1.1"
- Data!A2 = Link to a separate Workbook, stored on SkyDrive, which contains the most recent version of the Tool, which I will keep updated as I update the Tool, e.g. "Version 1.2"
- Data!A3 = Formula: IF(Data!A1 <> Data!A2,"No","Yes")
Then I will have some code in the Workbook_Open event of the Pricing Tool, which updates the linked reference in Data!A2, and then checks to see if Data!A3 = "Yes" or "No".
If Data!A3 = "No", then a msgbox will appear explaining that the Tool is out-of-date, and they need to use the latest version. If Data!A3 = "Yes" - nothing happens, and the Pricing Tool opens as normal.
I have the Skydrive workbook saved here: https://d.docs.live.net/f7cb40d31b9852bd/Public/Pricing Analysis Tools Current Versions.xlsm
The most up-to-date version information is contained within Cell C7.
However, I'm running into two major issues with my code:
1. When I open the Pricing Tool, I get the "External Links" warning message, asking to update or ignore - which I don't want users to have to see (I want them to always update).
2. The VBA doesn't seem to allow me to automatically access the Skydrive file, I need to enter a username and password. Again, I don't want users to have to go through that - it should be a relatively quick and automatic process. Either they see a pop-up warning to confirm they need to update, or they see nothing, and they use the Tool.
If anyone can help me to resolve the issues with my current idea, or provide a different solution which would work - I would be most grateful.
nb - the only reason I am using SkyDrive is because it is one of the only "publicly" available hosting sites I could think of, which should mean that the cell could be updated regardless of how/when the user is opening the Tool (provided, of course, they have internet access). I can't host on our company's internal network, as, when working out of the office (as our Sales Reps do), they would have to actively log-in to the network, which, for us, is very troublesome and slow - so more often than not they won't do it. So SkyDrive seemed like the most "bullet-proof" method of ensuring that they could access the external link containing the most up-to-date version information.
Many thanks,
AP
