Hello,

I have a dropdown, let's say on row 4, and it's cell link is cell A4. When I
insert a row before row 4 (via macro), then the dropdown moves with all rows
one row below and is located now on row 5, as it's cell link (cell A5). The
thing is, that the cell link value does not move down with dropdown, but
remains in cell A4. Is there any workaround, because I need that the cell
link values would move with the dropdown, as it is.

I cannot duplicate your problem.
Suggest you examine your code to see if it is adding the value to the cell.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"taavo"
wrote in message
Hello,
I have a dropdown, let's say on row 4, and it's cell link is cell A4. When I
insert a row before row 4 (via macro), then the dropdown moves with all rows
one row below and is located now on row 5, as it's cell link (cell A5). The
thing is, that the cell link value does not move down with dropdown, but
remains in cell A4. Is there any workaround, because I need that the cell
link values would move with the dropdown, as it is.

I still can't duplicate your problem.
I used your code and inserted 3 dropdowns.
I manually assign the linked cells to the same row and then assign
the input range.
Whether I manually insert a row or do it with code, the dropdowns
move down and the linked cells move and the values in the
linked cells remain the same. There are no left over values
in cells above.
On the properties tab for each control, "move but don't size with cells"
is selected. Also, the assigned ranges are in rows below the dropdowns.

I tested this in XL2002. Perhaps, you are using XL2007?
It seems to march to a different drummer.
Also, are you inserting an entire row or just the cells above the dropdowns?
That could screw things up.
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"taavo"
wrote in message
let me specify.
I use following rows in macro

With ActiveSheet
For Each cell In Range(Destination)
Set uusDropDown = .DropDowns.Add(cell.Left, cell.Top, cell.Width,
15.75)
Next cell
End With

to create dropdowns on a worksheet: choosing an item in last dropdown in a
column creates a new dropdown onto next row beneath it. Then I assign first
cell of a row the dropdown is located on, to be a cell link for this dropdown.

Now if I insert a row before those dropdowns(no difference, if I insert a
row via macro or directly on the worksheet)- behavior of the cell link is the
same- all cell link addresses move down one row with all other content as
expected, but cell link values values do not.

I'm not very good at explaining this, but for example: I have 3 dropdowns,
on rows 10,11 and 12, and cell links are located accordingly in cells A10,
A11 and A12. Third row is selected in first, second row in second and first
row in third dropdown- so A10=3, A11=2 and A12=1. If I insert a row before
row 10 on a worksheet, then dropdowns and cell link addresses move one row
down, but not cell link values. Now dropdowns are located on rows 11, 12 and
13; their cell links in A11, A12 and A13. But cell values do not change
accordingly- A10=3, A11=2 and A12=1 thus previously selected rows in
dropdowns are changed and this is not acceptable.
With this case I expect that A11=3, A12=2 and A13=1 so the rows selected in
dropdowns would remain unchanged.
So can it be that I'm missing omething at it can be done?

Seems that I found where this behaviour comes from after all.

All dropdowns in my worksheet have macros assigned. As soon as I removed the
assignments from dropdowns, cell links started to behave "normally".
I guess I have to use worksheet change event instead and everything will be OK

Taavo