Items From Column Headers To Be Listed In ComboBox

[Excel Fox]

Clearly. Anybody else would like to chip in and understand what Jeff has in mind?

[Excel Fox]

You've already done what I've mentioned in post #7 above. not sure what else you are after.

[jeff]

Hi
If I select Sports from dropdown list then it appears in cells D4:D9 (heading + sports names lists) exactly like the file attached shows.If I select Animals from the dropdown list then it will show in cells D4:D9 (heading + animal list of names)...I want to be able to select Sports or Animals or Vehicles or Weather and which ever name is selected then the Heading + names from that group appears in D4:D9....I would then repeat the process for all 4 columns

I have not probably explained myself too well but hopefully this time..

Thanks

Jeff

[LalitPandey87]

Do you need VBA or by using only formula and why don't you use Form control unsted of using ActiveX control. Can you arrange data as below if you need Formula :

SPORTS	FOOTBALL
SPORTS	RUNNING
SPORTS	BASEBALL
SPORTS	BASKETBALL
SPORTS	GRIDIRON

ans so on.

[jeff]

Thank you
Ok i used a Active X Listbox as this i have linked to C10....whenever i select a name from the Listbox it then appears in C10.......this is what i require....if i select Animal from Listbox it places Animal in C10 and then it needs to look at Lists sheet for Animal and when it finds Animal it then places all the names from Animal
DOG
CAT
HORSE
LION
TIGER
COW
into cell range Groups!C11:C16...it will do the same for the other 3 Names in the Listbox....

Hope this helps

Jeff

[Ingolf]

Hi,

Is this what you want? See attach.

[LalitPandey87]

You can use below code to get list based on selection in drop down control. For my self i have used Form control instead of Activex control as you have used. so please use user form control:

Sub GetList(ByVal strSheetName As String, ByVal strListBoxName As String, ByVal strOutputDataCell As String)    
    
    Dim varStrGroup()                   As Variant
    Dim varStrGroupItemsTemp()          As Variant
    Dim varStrGroupItems()              As Variant
    Dim varOutputItems()                As Variant
    Dim strListItemValue                As String
    Dim strItem                         As String
    Dim rngOutPutRange                  As Range
    Dim lngLoop                         As Long
    Dim lngLoop1                        As Long
    Dim lngCount                        As Long
    
    Const strDataSheet                  As String = "Sheet2"
    Const strGroupRangeName             As String = "GROUPS"
    Const strGroupItemRangeName         As String = "GROUPSITEMS"


    With ThisWorkbook
        With .Worksheets(strDataSheet)
            varStrGroup = .Range(strGroupRangeName).Value
            varStrGroupItemsTemp = .Range(strGroupItemRangeName).Value
        End With
        With .Worksheets(strSheetName)
            strListItemValue = .DropDowns(strListBoxName).List(.DropDowns(strListBoxName).ListIndex)
            Set rngOutPutRange = .Range(strOutputDataCell)
        End With
        ReDim varStrGroupItems(1 To UBound(varStrGroupItemsTemp), 1 To 2)
        For lngLoop = LBound(varStrGroupItemsTemp) To UBound(varStrGroupItemsTemp)
            varStrGroupItems(lngLoop, 2) = varStrGroupItemsTemp(lngLoop, 1)
        Next lngLoop
        For lngLoop1 = LBound(varStrGroup) To UBound(varStrGroup)
            For lngLoop = LBound(varStrGroupItemsTemp) To UBound(varStrGroupItemsTemp)
                If LCase(Trim(varStrGroupItemsTemp(lngLoop, 1))) = LCase(Trim(varStrGroup(lngLoop1, 1))) Then
                    varStrGroupItems(lngLoop, 1) = varStrGroup(lngLoop1, 1)
                    Exit For
                End If
            Next lngLoop
        Next lngLoop1
        For lngLoop = LBound(varStrGroupItems) To UBound(varStrGroupItems)
            If LenB(strItem) = 0 Then
                strItem = varStrGroupItems(lngLoop, 1)
            Else
                If LenB(varStrGroupItems(lngLoop, 1)) <> 0 Then
                    If LCase(strItem) <> LCase(varStrGroupItems(lngLoop, 1)) Then
                        strItem = varStrGroupItems(lngLoop, 1)
                    End If
                End If
            End If
            If LenB(varStrGroupItemsTemp(lngLoop, 1)) <> 0 Then
                varStrGroupItems(lngLoop, 1) = strItem
            End If
        Next lngLoop
        lngCount = 0
        ReDim varOutputItems(1 To UBound(varStrGroupItems), 1 To 1)
        For lngLoop = LBound(varStrGroupItems) To UBound(varStrGroupItems)
            If LCase(varStrGroupItems(lngLoop, 1)) = LCase(strListItemValue) Then
                lngCount = lngCount + 1
                varOutputItems(lngCount, 1) = varStrGroupItems(lngLoop, 2)
            End If
        Next lngLoop
        With .Worksheets(strSheetName)
            .Range(rngOutPutRange, .Cells(.Rows.Count, rngOutPutRange.Column).End(xlUp)).ClearContents
        End With
        If lngCount > 0 Then
            rngOutPutRange.Resize(UBound(varOutputItems), 1).Value = varOutputItems
        End If
    End With
    
    Erase varStrGroup
    Erase varStrGroupItemsTemp
    Erase varStrGroupItems
    Erase varOutputItems
    strListItemValue = vbNullString
    strItem = vbNullString
    Set rngOutPutRange = Nothing
    lngLoop = Empty
    lngLoop1 = Empty
    lngCount = Empty


End Sub

Below is the method to call above procedure

Sub evt_ListBox_1()


    Call GetList("Sheet1", "cboList1", "D4")


End Sub

In my case first drop down control name is cboList1 so change accordingly.

[bakerman]

I simply used defined named ranges and the listindex of your listbox.
You can make these named ranges dynamic so you can add or delete items freely.

[jeff]

Thanks Lalitpandy87....i will attempt your code soon

Hi Ingolf and Bakerman.Both excellent
Ingolf and Bakerman if i want to add another 8 identical lists across to Column K what would be the best way.....i want 9 lists but all exactly the same?eg...if i want all 9 lists to show Weather then i select Weather but i think i would have to have a Listbox for each column (9)....i want the 9 lists to all be independant of each other but with the option of showing any combination
Ingolf and Bakerman how do i get the listbox to remain the same size all the time...when i close the sheet the List box is smaller when i reopen
Bakerman how do i make the text larger in the cells.I used the increase Font but it goes back to small size

Sorry for questions but this is the set up i wanted

Jeff

[jeff]

Hi Bakerman

If I select below the list in the listbox the code gives an error.Application defined or object defined error....

Also please will your code support another 4 identical groups...

Also how can I make the Fonts larger,,,,it did not stay large after I used the enlarge font button

Can the listbox have a constant size box...it changes after the workbook is closed....when I re-open the list box is smaller...I locked the aspect ration and do not move cells and resize....still changes size

Thank you for your help

Jeff