Need VBA Help- To select and retain the valid values from the list

[Anna Q]

Hi ,

I have the following code, which allows me to select the values from the list. In case I type a value not in the list, it should not allow me to take the value and the cursor should not move to the next cell .
How do I achieve this?

Dim a()

Private Sub ComboBox1_Change()
 If Me.ComboBox1 <> "" And IsError(Application.Match(Me.ComboBox1, a, 0)) Then
    Set d1 = CreateObject("Scripting.Dictionary")
    tmp = "*" & UCase(Me.ComboBox1) & "*"
    For Each c In a
      If UCase(c) Like tmp Then d1(c) = ""
    Next c
    Me.ComboBox1.List = d1.keys
    Me.ComboBox1.DropDown
 End If
 ActiveCell.Value = Me.ComboBox1
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not Intersect(Me.Range("$E:$E"), Target) Is Nothing And Target.Count = 1 Then
    a = Application.Transpose(Sheets("Sheet2").Range("List2"))
    Me.ComboBox1.List = a
    Me.ComboBox1.Height = Target.Height + 3
    Me.ComboBox1.Width = Target.Width
    Me.ComboBox1.Top = Target.Top
    Me.ComboBox1.Left = Target.Left
    Me.ComboBox1 = Target
    Me.ComboBox1.Visible = True
    Me.ComboBox1.Activate
    
  Else
    Me.ComboBox1.Visible = False
  End If
 End Sub

Private Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  ComboBox1.List = Sheets("Sheet2").Range("List2").Value
  Me.ComboBox1.DropDown
End Sub
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  If KeyCode = 13 Then ActiveCell.Offset(1).Select
End Sub

Thank You .