how to use ComboBox1.List = Worksheets("SHEET1").Range("A1:A50").Value in this code

[moradiyavm]

Private Sub ComboBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call DropDown(KeyAscii.Value)
End Sub


Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode.Value = 8 Then
Call DropDown(KeyCode.Value)
End If
End Sub


Private Sub UserForm_Initialize()
Dim MyArr As Variant
MyArr = Array("Adam 001", "Adam 002", "Apple 001", "Apple 002", "Banana 001", "Banana 002", "Orange 001", "Orange 002")
ComboBox1.List = MyArr
End Sub


Sub DropDown(NKey As Integer)
If NKey <> 8 Then
MyV = Chr(NKey)
MyC = 1
Else
MyV = ""
MyC = 0
End If
Dim MyArr As Variant
MyArr = Array("Adam 001", "Adam 002", "Apple 001", "Apple 002", "Banana 001", "Banana 002", "Orange 001", "Orange 002")
For a = LBound(MyArr) To UBound(MyArr)
If UCase(Left(MyArr(a), Len(ComboBox1.Value) + MyC)) = UCase(ComboBox1.Value & MyV) Then
MyStr = MyStr & MyArr(a) & "~"
End If
Next a
If MyStr <> "" Then MyNewArr = Split(MyStr, "~", , vbBinaryCompare)
On Error Resume Next
MyL = UBound(MyNewArr)


If MyL <> "" Then
ComboBox1.List = MyNewArr
Else
ComboBox1.List = Split("", "")
End If
End Sub

 

[moradiyavm]

Private Sub ComboBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call DropDown(KeyAscii.Value)
End Sub


Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode.Value = 8 Then
Call DropDown(KeyCode.Value)
End If
End Sub


Private Sub UserForm_Initialize()
Dim MyArr As Variant, i As Long
MyArr = Worksheets("SHEET1").Range("A1:A50").Value
For i = 1 To UBound(MyArr, 1)
ComboBox1.AddItem MyArr(i, 1)
Next
End Sub


Sub DropDown(NKey As Integer)
If NKey <> 8 Then
MyV = Chr(NKey)
MyC = 1
Else
MyV = ""
MyC = 0
End If
Dim MyArr As Variant
MyArr = Worksheets("SHEET1").Range("A1:A10").Value
For a = LBound(MyArr) To UBound(MyArr)
If UCase(Left(MyArr(a), Len(ComboBox1.Value) + MyC)) = UCase(ComboBox1.Value & MyV) Then 'subscript out of range
MyStr = MyStr & MyArr(a) & "~"
End If
Next a
If MyStr <> "" Then MyNewArr = Split(MyStr, "~", , vbBinaryCompare)
On Error Resume Next
MyL = UBound(MyNewArr)


If MyL <> "" Then
ComboBox1.List = MyNewArr
Else
ComboBox1.List = Split("", "")
End If
End Sub