Populate combo box from selection of different combo box

[bdsii]

Any help would be appreciated. :yes

I have a simple userform where the user would select the Street on the first combobox. Based on the selection in the first combobox, the user could select the Address in the second combobox. The Address combo box would be limited to only those addresses that match the street selected in the street combo box. Once the address combo box has been selected, some code would then populate the textbox Name with the value in the Name field.

I am attaching a sample file with the userform and current code I have cobbled together. If improvements can be made to the code populating the comboboxes that could be appreciated too.

One issue is that for the street combobox, I would only want to see one instance of each street name, not one instance for each time it appears in the data. For example if there are 5 instances of an address on the street named Alpha, I would want to only see Alpha one time in the street combobox.

If it is possible to sort the items populating the comboboxes so they would be in alphanumeric order that would help if items are added at the end of the source data in a random order. I suppose I could sort the source data before initializing and loading the comboboxes but if there is code to help with that in the load process that may work better.

I am not certain this sample requires a combobox versus a listbox. In a later sub I want to utilize the variables and data selected here so if it is easier to use a listbox then I can switch to listboxes versus comboboxes.

I really have no idea how to populate the Name textbox and have left that sub blank for the code I need.

Ideas / Suggestions ?

thanks!

[lucas]

What's the process button for?

[lucas]

Oh, I see, it's to populate the name textbox......sorry, it's early.

[lucas]

This seems to work for the comboboxes. As I said, I don't have 2007 so you will have to let me know if it works.

see attached

[bdsii]

Thanks Lucas, that does work for the combo boxes.

Any ideas how to handle getting the Name value returned in the text box ?

[bdsii]

I think I have the last portion figured out. I was making it too hard when I didn't need to. The code below populates the nametextbox correctly.


I tested the code populating the combo boxes and it is limited correctly but the data to select from is not sorted. It is displayed in the order in the original data. Is it possible to get that data sorted ?


thanks!


Private Sub Process_Click()

Dim Counter As Long
Dim totalrows As Long
Dim streetcell As String
Dim addresscell As String
totalrows = ActiveSheet.UsedRange.Rows.Count

Sheets("Sheet1").Select
Range("A1").Select
For Counter = 2 To (totalrows - 1)
Range("A" & Counter).Select
streetcell = ActiveCell.Value
Range("B" & Counter).Select
addresscell = ActiveCell.Value
If streetcombo = streetcell And addresscombo = addresscell Then
Range("C" & Counter).Select
nametextbox = ActiveCell.Value
Exit Sub
Else
' Do Nothing
End If
Next Counter
End Sub

[GTO]

Could you re-post your wb in .xls format? Along with Steve, there are plenty who don't have 2007; you may get more responses/interaction that way.

Mark

[bdsii]

Good suggestion of uploading it in a different format. Attached is the updated version in .xls

I think the last thing left is to try to sort the data in the drop down box so the user selects from an alphanumeric sort of data?

Is this something a bubble sort would help with? I am totally lost how to solve that part if it is even possible. :think:

thanks!

[Tinbendr]

I've been following this as I have similar interest in cascading combo boxes.

Use Find instead of a loop to fill NameTextbox. If the list gets long, Find will be faster.

Private Sub Process_Click()
Dim C As Range
Dim FirstAddress As String

With Range("A:A")
Set C = .Find(streetcombo, LookIn:=xlValues)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
If CStr(C.Offset(, 1)) = findname.addresscombo Then
nametextbox = C.Offset(, 2)
Exit Sub
End If
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> FirstAddress
End If

End With
End Sub
I have found a sorting solution, but can't make it work just yet. I'll post back when I get it resolved.

[lucas]

bdsii, I'm sorry I haven't revisited this but I got busy and haven't been to the forum for a day or two.

Looks like you found a method. You have a lot of selects and it might be slow on large sheets of data. Tinbendr's solution is much cleaner and fast.

[Tinbendr]

OK, got it working.

I rewrote most of it to suit the sorting routine (http://www.j-walk.com/ss/excel/tips/tip47.htm) and, well, me!

I moved the Process routine to a change event. Used that commandbutton to display the results of the selections.

GTO, I learned something new this weekend. The Scripting dictionary that you use is available in VBA as a Collection. See Module1 for the code (or the link above). It uses the same Count and Item methods as the DIC.

Enjoy!

[bdsii]

Wow, good suggestions, I appreciate it! I have learned gained several new weapons for my meager arsenal of VBA :-)

I will take a look and may have questions about it so I can understand the why of the code.

Thanks to all!