Adding a VLookup to an AutoComplete ComboBox
 

I am using a function that generates an activex combobox to provide an autocomplete capability on top of the data validation.

I want to add a vlookup, so that the selected entry gets looked up in a table, and the result is what eventually fills the cell.

I cant figure out where to put my vlookup function.

any ideas?

Function I want to use (not even sure how to reference the combobox value)

WorksheetFunction.VLookup((???), "D5:E749", 2, False)

Macro for combo box:


'==========================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Cancel = True
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
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 = ws.Range(str).Address
.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