Hello,

So i am trying to make a part list that you can use a drop down with the part number, or the description. that will auto fill the other side with the correct matching thing. I have that part of my code working good. This is that code.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim prt As Range, rng As Range
Set prt = Target.Parent.Range("a6:n40")                             'Set Target Range to watch
    Application.ScreenUpdating = False
    'wspartlist.Unprotect Password:="stlFRAC"
    On Error GoTo errHandler
    If Target.Count > 1 Then GoTo exitHandler                      'only watch single cell changes
    If Intersect(Target, prt) Is Nothing Then GoTo exitHandler     'Only look at that range
    If Target.Address = Target.MergeArea(1).Address Then            'Watch only the first column of the merged rows
        For Each rng In Target
            Select Case rng.Column
                Case Is = 1, 8
                    If Not IsError(Application.match(rng.MergeArea(1).Value, wsPartList.Range("partno"), 0)) Then
                        Application.EnableEvents = False
                        rng.Offset(0, 1).Formula = "=index(tblPart,match(" & rng.Cells(1).Address & ",partNo,0),2)"
                        rng.Offset(0, 1).Formula = rng.Offset(0, 1).Value
                        Application.EnableEvents = True
                    End If
                Case Is = 3, 10
                    If Not IsError(Application.match(rng.MergeArea(1).Value, wsPartList.Range("partdesc"), 0)) Then
                        Application.EnableEvents = False
                        rng.Offset(0, -2).Formula = "=index(tblPart,match(" & rng.Cells(1).Address & ",partDesc,0),1)"
                        rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value
                        Application.EnableEvents = True
                    End If
            End Select
        Next rng
    End If
    'wspartlist.Protect Password:="stlFRAC"
    Application.ScreenUpdating = True
    Application.EnableEvents = True
Exit Sub

exitHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
Exit Sub

errHandler:
    MsgBox "error" & " " & Err.Number
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
Then I wanted to add a autocomplete thing. so if someone starts typing it will help the user find the right part. I found this on another website and thought maybe I could have both sub's in the working, it works on selection change, mine works on value change. Although they have a double click version i haven't tried. Below is the code.

www-contextures-com-xlDataVal14-html (website that i can't link because im new here. but i think it is important for my reference)

Option Explicit
' Developed by Contextures Inc.
' www-contextures-com
Private Sub TempCombo_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
    'Hide combo box and move to next cell on Enter and Tab
    Select Case KeyCode
        Case 9
            ActiveCell.Offset(0, 1).Activate
        Case 13
            ActiveCell.Offset(1, 0).Activate
        Case Else
            'do nothing
    End Select

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim Tgt As Range
Dim TgtMrg As Range
Dim c As Range
Dim TgtW As Double
Dim AddW As Long
Dim AddH As Long

Set ws = ActiveSheet
On Error Resume Next
'extra width to cover drop down arrow
AddW = 15
'extra height to cover cell
AddH = 5

If Target.Rows.Count > 1 Then GoTo exitHandler

Set Tgt = Target.Cells(1, 1)
Set TgtMrg = Tgt.MergeArea
On Error GoTo errHandler

  Set cboTemp = ws.OLEObjects("TempCombo")
    On Error Resume Next
  If cboTemp.Visible = True Then
    With cboTemp
      .Top = 10
      .Left = 10
      .ListFillRange = ""
      .LinkedCell = ""
      .Visible = False
      .Value = ""
    End With
  End If

  On Error GoTo errHandler
  If Tgt.Validation.Type = 3 Then
    Application.EnableEvents = False
    If Not TgtMrg Is Nothing Then
      'get total width of merged cells
      TgtW = 0
      For Each c In TgtMrg.Cells
        TgtW = TgtW + c.Width
      Next c
    End If
    
    str = Tgt.Validation.Formula1
    str = Right(str, Len(str) - 1)
    With cboTemp
      .Visible = True
      .Left = Tgt.Left
      .Top = Tgt.Top
      If TgtW <> 0 Then
        'use total width for merged cells
        .Width = TgtW + AddW
      Else
        .Width = Tgt.Width + AddW
      End If
      .Height = Tgt.Height + AddH
      .ListFillRange = str
      .LinkedCell = Tgt.Address
    End With
    cboTemp.Activate
    Me.TempCombo.DropDown
  End If

exitHandler:
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  Exit Sub
errHandler:
  Resume exitHandler

End Sub
When I use the auto complete however, my value change code doesn't fire. Is there a better way?

Thanks