Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding ComboBox programitacally | Excel Programming | |||
Data Validation Combobox w/autocomplete | Excel Discussion (Misc queries) | |||
AutoComplete - Forms Combobox | Excel Worksheet Functions | |||
Adding to AutoComplete programmatically | Excel Programming | |||
3 possible methods for adding value to a combobox | Excel Programming |