I want to reference closed workbooks with combo boxes

  • Thread starter Thread starter Akira5284
  • Start date Start date
A

Akira5284

Howdy,

Is it possible to create a list in one workbook and then use anothe
workbook with a combo box to reference it? I know how to reference
single cell, but for some reason, when i try to do the same for a comb
box, it says "Reference is invalid" or something along those lines.

The reason i want to do it this way is so that updating the list ca
change the list across an entire server rather than having to updat
the list on individual computers.....this also brings me to a secon
problem.

When the list gets updated, the reference number change, so if i wa
referencing Name!$A3 and then i decide to insert a new row so that A
becomes A4, the value also changes so the new reference is to A4, i
there anyway to keep it so it references a certain range and does shif
in that manner.....like can i just reference 1-500 and if somethin
shifts then they all shift and the new entry is included?

Thanks for any vews and thanks for any replies. I also apologize if th
thoughts are jumbled.


Akir
 
Akira5284 wrote...
Is it possible to create a list in one workbook and then use another
workbook with a combo box to reference it? I know how to reference a
single cell, but for some reason, when i try to do the same for a combo
box, it says "Reference is invalid" or something along those lines.
Click to expand...
....

Ranges only exist in *open* workbooks. References to single area blocks
of cells in closed workbooks are resolved as arrays, not ranges. You
can't use references directly into closed workbooks. However, you could
use a range in the workbook containing the combo box as an interface,
with your combo box referring to that range, and that range containing
formulas referring to a range in a possibly closed workbook. AFAIK,
that's the best you can do.
When the list gets updated, the reference number change, so if i was
referencing Name!$A3 and then i decide to insert a new row so that A3
becomes A4, the value also changes so the new reference is to A4, is
there anyway to keep it so it references a certain range and does shift
in that manner.....like can i just reference 1-500 and if something
shifts then they all shift and the new entry is included?
Click to expand...

This is unclear. If your combo box were in workbook A and your list
range initially in workbook B, and if you had selected the 3rd entry in
the list in B (presumably while B was open), then you inserted an entry
above the 3rd row in the list in B, your stored index in your linked
cell will still contain the same index, which now points to something
other than what you had selected. There's no simple fix for this. The
index stored in the linked cell when you select an item in the combo
box
drop-down list is a constant number, and form controls aren't built
into
the recalculation process, so they're unaware of such changes in the
input range.

Your only option is to use event handlers to make them sensitive to
recalculation. You need to use the Change event handler to store the
input range address and the value in that list given by the index
number
in the linked cell. You need to use the Calculate event handler to
check
the current (recalc time) address of the input cell range address and
the value given by the index number in the linked cell against the
stored values, and if either differ, change the value in the linked
cell
and store the changed input range address.

This is messy and redundant, but that's what you get when trying to
incorporate form controls into the recalc mechanism. Dynamic data
sources
for controls are a major PITA for simplistic user interfaces like
Excel.
You'd be better off using a browser-based interface and a real
scripting
language and UI controls.
 
Back
Top