ComboBox, linking cells, vba, worksheetchange function, and overal frustration
 

Hello!

I've got a bit of weird problem he

to adjust a massive database, a user can enter a numeric ID on a
separate sheet, in a manual adjustment page.
when a user enters a numeric ID number into the column C, the
worksheet is populated with a series of combo boxes. depending on the
id entered, you get either 3 or 4 combo boxes to the the right of the
object like so:


ID [book dropdown] [party dropdown] [mapping
dropdown] [type of adjustment]


for now, I'm just loading all the comboboxes, and setting type of
adjustment to invisible when I don't need it
with the data form the drop downs and ID, I use VLOOKUP to find
further values about those IDs and display those further to the right.

the problem: linking the values of the comboboxes to the cells beneath
them so that I can use Vlookup. usually this would be relatively
straightforward (linking the value of the combobox to a range). but my
life is never simple...

ok, so the dillema. since there are several sections of types of
numeric IDs that the user could enter, I've got about five sections of
data like so:

London accounts
--------------
id
id
id
id

US accounts
---------------
id
id
id

Canada accounts
---------------
id
id
etc.


since a user would not necessarily enter adjustments into each section
every time, and the user may enter adjustments out of linear order, I
need a way of keeping track of which combobox went where so that I can
then link that combobox to a cell and then preform calculations with
the values displayed in those cells. I'm not entirely sure how to
automatically link the combobox a cell, especially since the
combobox(i) and the cell you link to isn't necessarily the same each
time an adjustment is made.

I think that the easiest way to do this would be to automatically link
the combobox to the cell below it as it is pasted in. since I get the
comboboxes by copying the appropriate ones from a separate sheet, they
become "combobox(i)".

The question:: is there away to moniter what (i) is while copying a
combobox from another sheet?

I know how to set the value of the combobox to the cell, it's just
figureing which combobox I need to link that's got me stumped. I'm
also rather good at VBA (it's all I do all day) so this is driving me
nuts.

oh, and since it's such a huge database, any loops are really
agonizing to do. usually there are about 200-500 adjustments done each
time the database is updated.


sorry for the overly elaborate lead up to 1 question.
thanks
lar.