I'm kind of confused at what you want to do, but this may give you some ideas.
The combobox on the controltoolbox toolbar has that matchentry property. You
can validate the entry if it's typed in (to see if it matches the list). If it
doesn't (here's my confusion), you can add it to the bottom of your list (is
that what create on on this same sheet meant?).
Any way, in my workbook, I had two sheets. On sheet2, I put the combobox. I
rightclicked on the the sheet2 tab and pasted this code:
Option Explicit
Private Sub ComboBox1_LostFocus()
Dim res As Variant
Dim response As Long
With Me.ComboBox1
res = Application.Match(.Value, .List, 0)
If IsError(res) Then
response = MsgBox(prompt:="Add " & .Value _
& " to list of customers?", _
Buttons:=vbYesNo)
If response = vbYes Then
'add it to bottom of list of customers?
With Worksheets("sheet1")
.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Value _
= Me.ComboBox1.Value
Me.ComboBox1.List = .Range("a1", _
.Cells(.Rows.Count, "A").End(xlUp)).Value
End With
Else
.Value = ""
End If
Else
'if it matched, I put it in A1
Me.Range("a1").Value = .Value
End If
End With
End Sub
In the workbook_open event (in the ThisWorkbook module), I put this:
Option Explicit
Private Sub Workbook_Open()
Worksheets("sheet2").ComboBox1.MatchEntry = fmMatchEntryComplete
With Worksheets("sheet1")
Worksheets("sheet2").ComboBox1.List _
= .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)).Value
End With
End Sub
The validation of the change in the combobox occurs when the user clicks outside
that combobox.
Steve Moulton wrote:
Greetings,
I was not sure which group to ask this question, so I posted in the
two most likely groups.
I am looking for a way to search though a column of about 2000
customer names to either choose one or find out if there is no such
customer listed. If there is such a customer, then paste that name
into a cell on sheet that I am trying to fill out. If on such
customer, then I need to create one on this same sheet.
Because of the number of entries I must go though, the use of "Data
Validation" on the cell in question does not work well.
I am trying to use a Text box to attempt to type in the customers name
with a ComboBox showing the closest match with each letter that I type
in.
Does anyone have an example of how to do this or know of a site with
these kind of examples?
Any help would be most appreciated.
TIA
-Steve Moulton
--
Dave Peterson
