Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I Lock a Link?
Or how can I stop this very strange behaviour.
I have two Excel workbooks. One acts as a template (Edit_UI.xlsb) providing a user interface and storing user data. Various users can create their own files from this template (okay, I know an xlsb isn't a true 'template' but is essentially being used as one). The other is an AddIn containing the majority of the code to drive template and also a number of lists for use in some template validation drop-downs. The AddIn contains dynamic names to reference its lists. The template contains static names that refer to the AddIn's dynamic names and the template's validation dropdowns are set to use these local static names. All well and good and it works fine in the confines of my laptop or from a share drive. The users, though, need to access this via a web page so the template is replicated from the share drive to a web server - the AddIn is not, it remains on the share drive. So the user clicks a link to launch the template. They enable macros and the workbook.open event launches the AddIn from the share drive. Again, this works fine, no problem. However, validation dropdowns no longer work. On looking at the static names that reference the AddIn it can be seen that they are referencing a non-existant AddIn on the webserver rather than the open AddIn. e.g. the references look something like this... ='http://webservername/sub.../sub.../etc.../MY_Engine.xlam'! AccNameList instead of like this... =MY_Engine.xlam!CostType Incidentally, that 'CostType' name reference looks like that all the time. I.e. it isn't suffering the same malais as 'AccNameList' - possibly because it isn't a dynamic name. Having said that, Some dynamic names used to work while others didn't. Since trying to resolve the problem, though, none of the dynamic references remain unaffected. What I've tried: 1. Opened template on the webserver, changed the source of the rogue link to point to the correct location, saved. 2. The AddIn used to be replicated to the webserver, even though it was never accessed from that location (I don't know why but the original designer of the system insisted the AddIn must be used from the NT server share drive - I guess he has his reasons). I deleted the AddIn from the webserver location and prevented its further replication from the share drive. 3. Deleted and recreated all the affected names with different names, both in the AddIn and the template. 4. Forced both workbooks to recalculate when the validation cells are recalculated. Nothing seems to make a jot of difference so now I'm clutching at straws and wondering if there's a way to override Excel's 'cleverness' by writing the name reference in way that says "Don't change me - I'm exactly as I should be". Any other advice greatly appreciated. Nick. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i lock the external file (link) path so that it does not ch | Excel Discussion (Misc queries) | |||
how do i lock the external file (link) path so that it does not ch | Excel Discussion (Misc queries) | |||
is it possible to lock-link cells? | Excel Discussion (Misc queries) | |||
Can I link/lock rows so they move together in a sort? | Excel Worksheet Functions | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) |