Checkbox controlled dropdown

[Ramachandran]

In sheet1 I have a combobox (dropdown) with a list of names from sheet2 - Column A.
In sheet2 I have the names in column A, and some other values in column B - let's say apples and oranges.

What I would like to have is two checkboxes on sheet1, one with the text apples and one oranges. When both are True, I would like the dropdown list to show the names of both the apples and the oranges, if only the apples checkbox are true then the list shows only apples, and when none are true, the dropdown list is empty.

How may I acheive this?
Thanks

 

[AlphaFrog]

What version of Excel do you have?

There are two types of controls you can have on a worksheet. The ActiveX-type of controls from the Control Toolbox tool bar and the Form-type controls from the Form toolbar.
Overview of forms, Form controls, and ActiveX controls on a worksheet

What type of control are you using?

 

[Ramachandran]

A Form type control:
"ActiveSheet.Shapes.Range(Array("Drop Down 1")).Select"

Edit:
I foolishly decided to cross-post this thread on every excel forum I knew.
Links:
http://www.ozgrid.com/forum/showthread.php?t=159149
http://www.mrexcel.com/forum/showthread.php?t=586079

 

[AlphaFrog]

Assign this macro to both of the Form-type checkboxes (right-click on the checkbox and select assign macro).

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Apples_and_Oranges()<br>    <br>    <SPAN style="color:#00007F">Dim</SPAN> Cmb1 <SPAN style="color:#00007F">As</SPAN> Shape<br>    <SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>    <br>        <SPAN style="color:#00007F">Set</SPAN> Cmb1 = .Shapes("Drop Down 1")<br>        <br>        Cmb1.ControlFormat.RemoveAllItems<br>        <br>        <SPAN style="color:#00007F">If</SPAN> .Shapes("Check Box 2").DrawingObject.Value = xlOn <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Sheets("Sheet2").Range("A1:A5")  <SPAN style="color:#007F00">'Apples range</SPAN><br>                Cmb1.ControlFormat.AddItem cell.Value<br>            <SPAN style="color:#00007F">Next</SPAN> cell<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <br>        <SPAN style="color:#00007F">If</SPAN> .Shapes("Check Box 3").DrawingObject.Value = xlOn <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Sheets("Sheet2").Range("B1:B5")  <SPAN style="color:#007F00">'Oranges range</SPAN><br>                Cmb1.ControlFormat.AddItem cell.Value<br>            <SPAN style="color:#00007F">Next</SPAN> cell<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Ramachandran]

Thank you very much!
Just what I was looking for.

 

[Ramachandran]

And a little follow up:
Is it possible to Sort the items in the dropdown alphabetically?

I tried this one, without any luck.

Dim x As Integer, y As Integer, z As String
With Worksheets("Sheet1").Shapes("Drop Down 1").ControlFormat
For x = 0 To .ListCount - 2
For y = x + 1 To .ListCount - 1
If .List(x) > .List(y) Then
z = .List(y)
.List(y) = .List(x)
.List(x) = z
End If
Next y
Next x
End With

 

[AlphaFrog]

Sub Apples_and_Oranges()
    
    Dim Cmb1 As Shape
    Dim cell As Range
    Dim i&, j&, temp, v
    
    With ActiveSheet
    
        Set Cmb1 = .Shapes("Drop Down 1")
        
        Cmb1.ControlFormat.RemoveAllItems
        
        If .Shapes("Check Box 2").DrawingObject.Value = xlOn Then
            For Each cell In Sheets("Sheet2").Range("A1:A5")  'Apples range
                Cmb1.ControlFormat.AddItem cell.Value
            Next cell
        End If
        
        If .Shapes("Check Box 3").DrawingObject.Value = xlOn Then
            For Each cell In Sheets("Sheet2").Range("B1:B5")  'Oranges range
                Cmb1.ControlFormat.AddItem cell.Value
            Next cell
        End If
   End With
   
    ' Sort
    If Cmb1.ControlFormat.ListCount Then
        v = Cmb1.ControlFormat.List()
        Cmb1.ControlFormat.RemoveAllItems
        
        For i = 1 To UBound(v) - 1
            For j = i + 1 To UBound(v)
                If StrComp(v(i), v(j), vbTextCompare) = 1 Then
                    temp = v(i)
                    v(i) = v(j)
                    v(j) = temp
                End If
            Next j
        Next i
        
        For i = 1 To UBound(v)
            Cmb1.ControlFormat.AddItem v(i)
        Next i
    
    End If
      
End Sub