Solved

Excel Searchable Drop Down Data Validation

Posted on 2013-11-12
4
2,396 Views
Last Modified: 2013-12-16
On Sheet 1 I have a list of 8,000 company names in a standardized form (i.e. full name of company). Too many names for simple data validation.

On  Sheet 2, I have 16,000 for sale listings that each typically include an incomplete partial company name. (each listing on its own row)

I would like to add a column to Sheet 2 that contains a searchable drop down list in a cell in every row that enables the user to enter the partial name in the listings and find all matching company names and then pick off the correct standardized name. (This will be a manual process by the data entry team and I want to make it as efficient as possible).

I have already done several google searches and can't find a solution that provides a searchable drop down in each row of Sheet2. I found one that came close, but couldn't get it to work in every row.

I'm hoping an Expert here will have the answer. As long as it works in every row of Sheet 2, I am open to any solution e.g. with or without controls, with or without VBA.

Thanks in advance!
0
Comment
Question by:bobinorlando
  • 2
  • 2
4 Comments
 
LVL 14

Accepted Solution

by:
Zack Barresse earned 500 total points
ID: 39642846
Hi there,

I think it depends on how you define close or approximate matches. This is never an easy task in Excel, which likes to deal in absolutes. So can you define what is a match and what is not? Unfortunately there's no built-in tool or process you could utilize to parse a list into matches for a data validation list. You could do this with code, but you'd need to define that matching thing I mentioned. If you can do that we can code for it.

Regards,
Zack Barresse
0
 
LVL 1

Author Comment

by:bobinorlando
ID: 39645352
Autocomplete functionality would suffice.

Here is some code I found on contextures.com that comes pretty close. However, I'm not sure I pasted it into the right location in View Code. The instructions here

http://www.contextures.com/xlDataVal11.html


say to paste at the cursor location when right clicking and selecting View Code. However the cursor was positioned in the middle of an empty sub:

Private Sub ComboBox1_Change()
'cursor was located here
End Sub

And when I put the code there that throws a compile error when the cursor is in the combobox and certain keys are pressed e.g. backspace, esc, delete:

Only comments may appear after an end sub, end function or end property


So I moved the following code outside of that sub and left the empty sub there and it seems to work.

However it is set to enter the combobox after a doubleclick inside a cell in the column with the data validation. That is fine when first starting. However, to increase the speed of data entry, I would like to add a key combination to activate it.

So for example, when selecting a match from the list and pressing enter, currently the cursor moves down to the next cell in the column as is the normal behavior in Excel. That is fine, however to enable the drop down to open up it requires a double click of the mouse meaning the right hand has to leave the keyboard. This is where I would like Ctrl Enter to activate the Data Validation.

How would I do that?






'==========================
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
  Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("Prep for SQL") 'the name of my worksheet

Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
  With cboTemp
  'clear and hide the combo box
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
  End With
On Error GoTo errHandler
  If Target.Validation.Type = 3 Then
    'if the cell contains a data validation list
    Cancel = True
    Application.EnableEvents = False
    'get the data validation formula
    str = Target.Validation.Formula1
    str = Right(str, Len(str) - 1)
    With cboTemp
      'show the combobox with the list
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 5
      .Height = Target.Height + 5
      .ListFillRange = str
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
    'open the drop down list automatically
    Me.TempCombo.DropDown

  End If
 
errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub
'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = True

If Application.CutCopyMode Then
  'allow copying and pasting on the worksheet
  GoTo errHandler
End If

Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
  With cboTemp
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With

errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub
'====================================
'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
'***NOTE: if KeyDown causes problems, change to KeyUp

Private Sub TempCombo_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
    Select Case KeyCode
        Case 9 'Tab
            ActiveCell.Offset(0, 1).Activate
        Case 13 'Enter
            ActiveCell.Offset(1, 0).Activate
        Case Else
            'do nothing
    End Select
End Sub
'====================================
0
 
LVL 1

Author Comment

by:bobinorlando
ID: 39645389
By the way, I am using Excel 2010.
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 39652927
Do you have everything in a sample file you can post? Easier to test on that than me re-creating the wheel from scratch. :)

Zack
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now