Clearly. Anybody else would like to chip in and understand what Jeff has in mind?
Printable View
Clearly. Anybody else would like to chip in and understand what Jeff has in mind?
You've already done what I've mentioned in post #7 above. not sure what else you are after.
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
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.
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
Hi,
Is this what you want? See attach.
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:
Below is the method to call above procedureCode: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
In my case first drop down control name is cboList1 so change accordingly.Code:Sub evt_ListBox_1()
Call GetList("Sheet1", "cboList1", "D4")
End Sub
:cheers:
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.
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
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