Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dropdown Rowsource
I have a sheet with multiple columns and rows.
Column A is a category description Column B is a subcategory description. I'm setting up a userform with dropdown, or combo boxes. What i want is on the change event of box 1, i want to set the rowsource property of box 2 to return all subcategory values that have the same category value. For example A B 1 Over Me 2 Over You 3 Over Us 4 Under Them 5 Under Everyone If I select "Over" In box 1, i need the range "B1:B3" for rowsource in box 2. Help! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dropdown Rowsource
I think I'd take a slightly different approach. I'd put my categories in a
separate range (name it cate??). And I'd put each subcategory in it's own range (column?) and name them nicely (Over and under??). Then I could just change the list of entries based on that selection and those ranges: Option Explicit Private Sub ComboBox1_Change() Me.ComboBox2.Clear Select Case LCase(Me.ComboBox1.Value) Case Is = "over" Me.ComboBox2.List = Worksheets("sheet1").Range("Over").Value Case Is = "under" Me.ComboBox2.List = Worksheets("sheet1").Range("under").Value End Select End Sub Private Sub CommandButton1_Click() Unload Me End Sub Private Sub UserForm_Initialize() Me.ComboBox1.List = Worksheets("sheet1").Range("cate").Value End Sub But you could do it by looping through the rows, too: Option Explicit Private Sub ComboBox1_Change() Dim myCell As Range Dim myRng As Range With Worksheets("sheet1") Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With Me.ComboBox2.Clear For Each myCell In myRng.Cells If LCase(myCell.Value) = LCase(Me.ComboBox1.Value) Then Me.ComboBox2.AddItem myCell.Offset(0, 1).Value End If Next myCell End Sub Aechelon wrote: I have a sheet with multiple columns and rows. Column A is a category description Column B is a subcategory description. I'm setting up a userform with dropdown, or combo boxes. What i want is on the change event of box 1, i want to set the rowsource property of box 2 to return all subcategory values that have the same category value. For example A B 1 Over Me 2 Over You 3 Over Us 4 Under Them 5 Under Everyone If I select "Over" In box 1, i need the range "B1:B3" for rowsource in box 2. Help! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Listbox rowsource on Userform | Excel Discussion (Misc queries) | |||
???Help??? Userform.Listbox.rowsource = ??? | Excel Discussion (Misc queries) | |||
ListBox Rowsource Limitation?? | Excel Programming | |||
listbox rowsource | Excel Programming |