populate cbo box

[av8tordude]

I would to populate the combo boxes on a form as follows...

Display the names from BA11:BA500 in the "Merchant's Name" combo box (eliminating duplicates being displayed in the drop down view)

If the below request is possible...

Display the Country or State from BB11:BB500 in the Country/State combo box (only populate the County or State name only and eliminating duplicates being displayed in the drop down view)
Display the City or Town from BB11:BB500 (only populate the County or State name only and eliminating duplicates being displayed in the drop down view)

Can some provide a code that will accomplish this. Thank you for your help

[GTO]

...names from BA11:BA500 in the "Merchant's Name" combo box (eliminating duplicates being displayed in the drop down view)

If the below request is possible...

Display the Country or State from BB11:BB500 in the Country/State combo box (only populate the County or State name only and eliminating duplicates being displayed in the drop down view)

Display the City or Town from BB11:BB500 (only populate the County or State name only and eliminating duplicates being displayed in the drop down view)

Can some provide a code that will accomplish this. Thank you for your help

Hi Aviator,

I cannot see the logic in this. We would be splitting up bits of data that seem to me - definitely belong together. Am I missing something?

[av8tordude]

actually you are correct.

if you have Raleigh, North Carolina, put Raleigh in the City cbobox and put North Carolina in the state box. If there are multiple Raleigh, North Carolina, display Raleigh in the drop down view once. same thing with North Carolina.

If this is not possible, can you cod the Merchant box?

[Bob Phillips]

This will populate the name list



Private Sub UserForm_Activate()
Dim vecMerchants As Variant
Dim idxMerchants As Long
Dim lastrow As Long
Dim i As Long

With Worksheets("Sheet1")

lastrow = .Cells(.Rows.Count, "BA").End(xlUp).Row
ReDim vecMerchants(1 To lastrow - 10)
For i = 11 To lastrow

If IsError(Application.Match(.Cells(i, "BA").Value, vecMerchants, 0)) Then

idxMerchants = idxMerchants + 1
vecMerchants(idxMerchants) = .Cells(i, "BA").Value
End If
Next i

ReDim Preserve vecMerchants(1 To idxMerchants)

Me.cboName.List = vecMerchants
End With
End Sub


But why not just have a textbox for the location and look up the corresponding value?

[GTO]

...But why not just have a textbox for the location and look up the corresponding value?

I'll have to watch and see, but I am not getting this bit. The merchant/owner/corp would seem to me to be the core of the record. We ain't likely to successfully bill Raleigh on behalf of multiple businesses.

[Bob Phillips]

Yeah, pick the merchant, lookup the location.

[av8tordude]

Hi Guys, It not about choosing the merchant. XLD's code works great to what I'm trying to accomplish. After I type the first few letters of the merchants name after entering it the first time, It automatically populates the field the name of the merchant that fits closest to what I'm typing.

I was hoping to do the same with the State and City.

[Aussiebear]

So you are looking for auto complete for cells in the State and City columns?