Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Search Methods
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Available methods and properties through OLE interface | Excel Discussion (Misc queries) | |||
Leveling Methods | Excel Worksheet Functions | |||
How to set up easy lookup methods | Excel Discussion (Misc queries) | |||
Std.Dev.methods NOT WORKING | Charts and Charting in Excel | |||
Certain methods do not work | Excel Programming |