Solved

Excel Searchable Drop Down Data Validation

Posted on 2013-11-12
4
2,190 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

757 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

21 Experts available now in Live!

Get 1:1 Help Now