Excel Dropbox Help

[systematic]

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

 

[Dave Peterson]

I think you might be able to do what you want, but how about a different
approach.

I'm kind of confused at what's happening and when it should happen, but maybe
you could do all the work with just one drop down. Just have it move to the
selected cell and set things up based on that selected cell.

I put one dropdown from the Forms toolbar on a worksheet. I named it "Drop Down
1".

Then I rightclicked on the worksheet tab and selected view code. I pasted this
into that window:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myDD As DropDown
Dim myList As Range

If Target.Cells.Count > 1 Then Exit Sub

Set myDD = Nothing
On Error Resume Next
Set myDD = Me.DropDowns("drop down 1")
On Error GoTo 0

If myDD Is Nothing Then
Set myDD = Me.DropDowns.Add(0, 0, 0, 0)
myDD.Name = "Drop Down 1"
End If

If Intersect(Target, Me.Range("e:e")) Is Nothing Then
myDD.Visible = False
Exit Sub
End If

With Target
myDD.Top = .Top
myDD.Left = .Left
myDD.Width = .Width
myDD.Height = .Height
myDD.LinkedCell = .Cells.Address(external:=True)
myDD.Visible = True

Select Case .Value
Case Is = 1
Set myList = Me.Parent.Worksheets("inputs").Range("a2:a9")
Case Is = 2
Set myList = Me.Parent.Worksheets("inputs").Range("b2:b9")
Case Is = 3
Set myList = Me.Parent.Worksheets("inputs").Range("c2:c5")
Case Else
Set myList = Me.Parent.Worksheets("inputs").Range("a2:a9")
End Select

myDD.listfillrange = myList.Address(external:=True)

End With

End Sub

I was confused about how to determine the listfillrange, though.

Basing the listfillrange on that linked cell that may have been based on a
different range seemed kind of strange to me.