Hi all,

I'm hoping someone can help me out with a dropbox problem.

I am using two dropboxes on each row of data. The following code i
attached to the first dropbox -

sub dropdown10_change()

if cells(2, 5) = 1 then
activesheet.shapes(\"drop down 1\").select
with selection
.listfillrange = \"inputs!$a$2:$a$9\"

end with
end if

if cells(2, 5) = 2 then
activesheet.shapes(\"drop down 1\").select
with selection
.listfillrange = \"inputs!$b$2:$b$9\"

end with
end if

if cells(2, 5) = 3 then
activesheet.shapes(\"drop down 1\").select
with selection
.listfillrange = \"inputs!$c$2:$c$5\"

end with
end if

end sub

The dropbox is linked to the cell it sits in. So the code basicall
reads the number in the cell, and depending on what option was selecte
- decides what to populate the second drop box with.

The problem has stemmed from my need to copy these drop boxes down (u
to 1000 times). Is there some way to -

a) Automatically update the cell link in the format control of the dro
box as I fill the box down (ie. update from A1 to A2 when dragged down)

b) Instead of using "Cells(2,5)", use a bit of code to say - 'whateve
cell the drop box is linked to'.

c)Overcome the naming problem with filling the boxes down. ie. box i
A1 and A2 are 'Dropbox 10', so instead of referring to them by name
refer to them as 'selected drop box'.

Hope this makes sense. I don't think I can do what I want to d
(without programming the macro and renaming each drop box). I think th
only solution is for me to have one data entry sheet, and another fo
storing the info. - this seems more practical.

Any advice would be appreciated!

Cheers

systematic :eek

--
systemati
-----------------------------------------------------------------------
systematic's Profile: http://www.excelforum.com/member.php...fo&userid=2529
View this thread: http://www.excelforum.com/showthread.php?threadid=48940