Reciprocal Comboboxes

Private isarrow As Boolean
Option Explicit
Dim myarray
Private Sub cmbCode_Change()




Dim i As Long, sh As Worksheet, st As Worksheet, lr As Long
    With Me.cmbCode
       If Not isarrow Then .List = myarray 'Range("code").Value
        If .ListIndex = -1 And Len(.Text) Then
            For i = .ListCount - 1 To 0 Step -1
                If InStr(1, .List(i), .Text, 1) = 0 Then .RemoveItem i
            Next i
            .DropDown
        End If
    End With
    
    
Set sh = ThisWorkbook.Sheets("sheet1")
Set st = ThisWorkbook.Sheets("sheet2")




lr = sh.Range("A" & Rows.Count).End(xlUp).Row




For i = 2 To lr


If sh.Cells(i, "A").Value = (Me.cmbCode) Or _
    sh.Cells(i, "A").Value = Val(Me.cmbCode) Then
    cmbName.Value = sh.Cells(i, "B").Value
End If


If cmbCode.Value = Sheets("sheet1").Cells(i, "A").Value Then
    txtStock.Value = st.Cells(i, "B").Value
   


End If


    


Me.cmbCode.SetFocus
Me.cmbCode.DropDown
Next


End Sub


Private Sub cmbCOde_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    isarrow = KeyCode = vbKeyUp Or KeyCode = vbKeyDown
    If KeyCode = vbKeyReturn Then Me.cmbCode.List = myarray 'Range("code").Value
End Sub






Private Sub UserForm_Initialize()
Dim db, mycollection As Collection, cell As Range




    With CreateObject("scripting.dictionary")
        For Each db In Range("code").Value
            If Not .exists(db) Then
                .Add db, db
            End If
        Next
        myarray = .keys
    End With


End Sub