Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel Searchable Drop Down Data Validation

Posted on 2013-11-12
4
Medium Priority
?
3,678 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 1500 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

963 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