Solved: Help with ComboBox not in userform

[tatersmom]

If this combobox were on a userform, I'd have no problem with how to do this. But I need to have two comboboxes embedded on a worksheet. One will load a list of items from a range, not a problem. I have a list on a separate worksheet that I pull the data from.

The other, however, I am lost with. I need to load it from one of two different lists, depending on what item is picked from the first combobox list.

I tried writing a subroutine something like this, assuming the first combobox is linked to cell A1 and the two different lists to choose from are in cells F20:F23 and G20:G23.



sub comboBox1_Change()
if activesheet.range("A1").value = 1 then
comboBox2.list = range("F20:F23")
elseif activesheet.range("A1").value = 2 then
comboBox2.list = range("G20:G23")
end if
end sub


I get an error saying "Object Required" and highlighting one of the lins that starts with "comboBox2.list". I'm guessing it needs it's object (duh!). Is the object the sheet its on because I know it's not on a userform. When I change those two lines to read "ActiveSheet.comboBox2.list = ..." I get the error "Object doesn't support this property or method".

If the worksheet isn't the object, what is the object?

Also, the forms were built from the forms control menu, not the activex menu.

Thanks.

[mdmackillop]

Can you post your workbook?

[tatersmom]

I can't attach the actual file I'm working on because it's on a work server and not attached to the internet (not allowed to transfer it either).

I did attach an simple example of what I'm talking about though. In the combobox on the left you pick from the four kids' names. What I want the combobox2 to do is, depending on if a girl's name is chosen or a boys name is chosen, I want combobox2 to populate with the list in the GirlsToys range or the BoysToys range.

[mdmackillop]

Have a look at these.

[tatersmom]

This should do. I'll copy it over to my stuff tomorrow and mark this solved.

I didn't realize it considered the dropdown a shape.

Thanks so much.

[lucas]

I didn't realize it considered the dropdown a shape.



That's because you created them from the Forms toolbar. If you had used the control toolbox on the visual basic toolbar they would have been ActiveX controls complete with the same properties they would have on a userform.

me.comboBox2.list = range("F20:F23")
would have worked in that case as long as the code was in the sheet that held the combobox.

[mikerickson]

Neither VB, nor ActiveX controls are needed for this.

Link Drop Down 2 to the cell F29

Create a named Range
Name: myDependentRange
RefersTo: =CHOOSE(Sheet1!$F$28, Sheet1!$F$32:$F$34, Sheet1!$F$37:$F$39, Sheet1!$F$41:$F$43, Sheet1!$F$46:$F$48)

Then set the source range for Drop Down 3 to =myDependentRange

[tatersmom]

I hadn't anticipated the problem I'd have, but my lists are actually on a different worksheet so they can be protected and not changed. It proved a little more of a challenge than I thought it would.

Here's what finally worked for me.



Sub comboBox1_Change()
ActiveSheet.Shapes("ComboBox2").select

select case range("Cell name where ComboBox 1 results go")
Case 1
with selection
.ListFillRange = "Sheet2!$B$1:$B$3"
.LinkedCell = "$F$30"
.DropDownLines = 3
.Display3DShading = False
End With
Case 2
With Selection
.ListFillRange = "Sheet2!$B$6:$B$8"
.LinkedCell = "$F$30"
.DropDownLines = 3
.Display3DShading = False
End With
End Select

Range("A1").Select

End Sub


Thanks for helping me get there.