N
Nick H
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.
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.