Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 48
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do i lock the external file (link) path so that it does not ch Setagaya Japan Excel Discussion (Misc queries) 0 February 9th 09 06:47 AM
how do i lock the external file (link) path so that it does not ch Setagaya Japan[_2_] Excel Discussion (Misc queries) 0 February 9th 09 06:47 AM
is it possible to lock-link cells? petit.miette Excel Discussion (Misc queries) 2 December 22nd 08 08:18 PM
Can I link/lock rows so they move together in a sort? dla80 Excel Worksheet Functions 1 January 21st 08 04:02 PM
HELP! How do you--> Lock a set of rows but also link worksheets to FRUSTRATED Excel Discussion (Misc queries) 6 December 29th 04 11:05 PM


All times are GMT +1. The time now is 02:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"