-
1 Attachment(s)
Items From Column Headers To Be Listed In ComboBox
-
You will need to list them down in a range, OR use VBA to populate each combobox.
-
-
Just type each item from each group one below the other in a contiguous range, and pass that range as the RowSource for each of the comboboxes.
-
1 Attachment(s)
On sheet 1 I have 4 groups.In each dropdown box I require the 4 names of Sports,Animal,Vehicle and Weather to be available to select so that I can select any 4 groups at any time from any dropdown box.I showed 2 examples on the sheet on what It would look like ..
..
Thank you
-
-
If you want the category names, ie the column headers, in each of the dropdowns, then you need to list those four items vertically in a range, and then assign that range.
-
1 Attachment(s)
Thank you
How do I now link the headings to each group
Thank you
-
By going to the design mode, and then going to the properties window of each combobox, and in the ListFillRange, assign Sheet1!A1:A10 or any other range
-
Thanks
I think we are on a different page here..
Thanks for your help but I will try elsewhere
Jeff
-
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.
-
1 Attachment(s)
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.
-
1 Attachment(s)
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
-
1 Attachment(s)
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:
Code:
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
Code:
Sub evt_ListBox_1()
Call GetList("Sheet1", "cboList1", "D4")
End Sub
In my case first drop down control name is cboList1 so change accordingly.
:cheers:
-
1 Attachment(s)
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
-
1 Attachment(s)
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
-
1 Attachment(s)
Try this one. Above each column i've placed a Combobox which is filled with a dynamic named range.
On selecting a name the rest is filled in.
-
Hi Bakerman
Thank you
I get a error when selecting the dropdown box
This line is in yellow but it looks the same as the other code lines *
Sheets("LISTS").Range("Group" & ComboBox5.ListIndex + 1).Copy Range("G11")
Thanks
jeff
-
1 Attachment(s)
-
Hi Bakerman
Excellent.That perfect
Can you make the combobox fit the size of the column.So if the column is 10 units wide the combobox fits or it the column is changed from 10 to 20 wide can the combobox then resize to 20...
Also how do i get the text to be larger
Thanks again and much appreciated
Jeff
-
Automatically resize the CB's to Columnwidth NO, programmatically YES.
To enlarge the text goto designmode and select your CB, rightclick and select Properties, Select Font and there you can choose your Font size.
-
Thank you for all your help Bakerman
