VBA code - Override Cells of hard-entered data, based on VLookup results from hard data?

[SuperCarter]

Hi, All!

Carter here.
New to posting; long time forum reader.
Decently familiar with Excel formulas; aware of but new to VBA.


I have 2 Sheets. The 1st Sheet is for entering information into a Table for a Bill of Materials (BOM). One of the pieces of information for the BOM is the Vendor of the part. The 2nd Sheet holds a list of Vendors used by my company. The Vendor name in the BOM needs to be very specifically entered, to be referenced later by a Vlookup, to output a Vendor ID used in purchasing software. There are near 4,000 vendors and typing the vendor name perfectly every time is a big ask.

Using Data Validation and a drop-down list is inefficient because of the large number of names.
The ideal solution would be to have a drop-down list which auto-completed, but data validation does not do this.
I have seen the drop-down auto-complete solution of making a Combo Box. This does not seem feasible for my use, because it would mean making a new Combo Box for every line of the BOM, including when a new row gets inserted. Also, because I need to reference the selection of the vendor name later, each Vendor ID lookup would have to be unique to the corresponding Combo Box name.

I have dealt with this by making a helper column (column A), to hard-enter a close approximation of the vendor name. The official Vendor name column (column B) then uses a Vlookup to pull the exact name.
=IF(ISBLANK(A1),"",(VLOOKUP(A1&"*", VendorNames,1,0)))
Notes about the formula:
1. IF statement and IsBlank statement used to keep blank A cell from causing formula to result in 1st name in list.
2. "VendorNames" is a named range from the 2nd sheet.
3. Vlookup is using wildcard to get around the formula inherently rounding down from nearest match.


The helper column is undesirable, from an aesthetic point of view. Moving it off to the side is undesirable, user friendliness-wise. I would love to somehow merge these actions: hard-entering an approximation of the vendor name, and having the closest actual name override the cell.
Since a cell cannot have both a hard-entered value and a formula, it seems like it would have to be a VBA code to replace the approximate text. Does anyone have a proposed solution to this?
*Again, I am aware of VBA and can copy and paste code and make small alterations, but am not well versed in the language.


Thank you for any and all help!

 

[Jeffrey Mahoney]

Would you consider a blend of formulas to show only the names that match the user input, an ActiveX Combobox that appears when needed, and VBA to run the whole thing?

I have the framework for a dynamic searchable list that a combo box can use to allow a user to start typing a name and it will shorten the list as the user types. You could have the user double click any of the cells and the combo box could pop up in the cell, allow them to find the name, and disappear when finished, allowing vba to copy the found name into the current cell.

You seem like a person that has the wherewithal to create such a tool. This kind of Excel tool is not hard to manage once it's complete, but it does require more than a little patience to set it up. You need to create some dynamic named ranges, create a combo box linked to a cell, and create some event based VBA code to handle everything. I would lead you through the steps. The reason I'm hesitant to start posting is because of this being your first post; most of the "first posters" run with a scared look when I start posting something that isn't a simple solution.

Jeff

 

[SuperCarter]

Jeff,

That does sound like a good solution. I was not aware you could use a single ComboBox to push a value to different cells. I am going to familiarize myself with the ActiveX creation.
Any guidance or advice you have for your suggested route would be greatly appreciated!

Thanks again.

 

[SuperCarter]

UPDATE:

In my reading up on ActiveX ComboBox design, I found a structure very close to what Jeff described:
http://www.contextures.com/xlDataVal11.html (I hope it is acceptable to post links to other help sources; I could not find anything about it in Board rules or FAQ).

I have implemented it into my workbook and it works quite well.

'==========================Private Sub Worksheet_BeforeDoubleClick _
  (ByVal Target As Range, _
    Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet


Set cboTemp = ws.OLEObjects("VendorSearch")
  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 + 2
      .Height = Target.Height + 2
      .ListFillRange = str
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
    'open the drop down list automatically
    Me.VendorSearch.DropDown
  End If
  
errHandler:
  Application.EnableEvents = True
  Exit Sub


End Sub
'=========================================
Private Sub TempCombo_LostFocus()
  With Me.TempCombo
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
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
'Table with numbers for other keys
'such as Right Arrow (39)
'https://msdn.microsoft.com/en-us/library/aa243025%28v=vs.60%29.aspx


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
'====================================

My one hang up is that triggering the ComboBox requires double-clicking into a cell. My users would prefer to be able to Tab through cells while entering data, so the double-clicking could throw off the entry rhythm.

Does anyone know how to change the trigger in the VBA code so that Tab'ing into a data validation cell will put you in the ComboBox?

 

[Jeffrey Mahoney]

You can trigger it by simply selecting a cell. Do you want the combobox to always appear when you select that cell?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  'Do Something
End Sub

 

[SuperCarter]

Jeff,

That works great. I do think I want the combobox to always appear. That way, if a user selects the wrong name (maybe a name that is very similar and only 1 spot off), they can easily click back into the list and click the correct name.

I realized through some testing it out that I do not want the drop-down list open action to fire every time, so I commented that line out. When I click into an appropriate cell, the combobox is called (and is obvious because I formatted the combobox to have a unique Fill Color), but the drop-down list does not fire. Most of my users will have a semblance of the Vendor they are using, and do not need to see the first 20 of 4,000 names every time.

A side effect of my current overall build: when I click on a drop-down cell and then ESC or click elsewhere without selecting a Name, that cell gave me an alert of an error in the data validation. I suppose it did not like 0 being chosen as Name, when that is not on the list? I just turned the error warnings off on my data validation cells. The unique, specific Vendor ID that is called in the cell next to the name entry is a giveaway as to the Name entry being valid or not.


This solution is fantastic so far. Thank you, Jeff, for pointing my in the right direction and helping with that trigger change. I really need to study some of the common events.