Run-time error '381' Could not get the List Property.Invalid property array index

[04011988]

Hi, I am trying to work on macro for a leave application module but that error pop out. I hope you could help me. Thanks in advance. Here is my code:

Private Sub ComboBox1_Change()

Sheet3.Activate

Dim mytext As Long

mytext = Me.ComboBox1.List(Me.ComboBox1.ListIndex)
TextBox1.Text = Application.WorksheetFunction.VLookup(mytext, Range("A1:E69"), 2, 0)
TextBox2.Text = Application.WorksheetFunction.VLookup(mytext, Range("A1:E69"), 3, 0)
TextBox3.Text = Application.WorksheetFunction.VLookup(mytext, Range("A1:E69"), 4, 0)


End Sub

Private Sub cmdSave_Click()

Sheet4.Activate

If Range("A2") <> "" Then
Rows("2:2").Select
Selection.Insert Shift:=xlDown
End If

If Range("A2") = "" Then
Range("A2") = Me.ComboBox1.Value
Range("B2") = Me.TextBox1.Value
Range("C2") = CDate(Me.TextBox4)
Range("D2") = Me.TextBox2.Value
Range("E2") = Me.TextBox6.Value
Range("F2") = CDate(Me.txtStartDate)
Range("G2") = CDate(Me.txtEndDate)
Range("H2") = Me.ComboBox4.Value
Range("I2") = Me.TextBox5.Value
Range("J2") = Me.TextBox3.Value
End If

Me.ComboBox1.Value = ""
Me.TextBox1.Value = ""
Me.TextBox4.Value = ""
Me.TextBox2.Value = ""
Me.TextBox6.Value = ""
Me.txtStartDate.Value = ""
Me.ComboBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox3.Value = ""


Range("L1").Select

End Sub

Private Sub cmdClose_Click()

Unload Me

End Sub

Private Sub cmdReset_Click()

TextBox1.Value = ""
TextBox4 = ""
TextBox2 = ""
TextBox6 = ""
ComboBox4 = ""
TextBox5 = ""
TextBox3 = ""

End Sub


Private Sub UserForm_Click()

TextBox4.Text = Format(Now(), "Short Date")

End Sub

 

[dmt32]

Hi,
welcome to forum

try changing

Dim mytext As Long


mytext = Me.ComboBox1.List(Me.ComboBox1.ListIndex)

to this

Dim mytext As Variant


mytext = Me.ComboBox1.Text

and see if solves the issue

Dave

 

[04011988]

Thank you Dave for welcoming me in this group and thanks for the help. However, when I changed the code, this error prompt:

Run-time error '1004': Unable to get the Vlookup property of the WorksheetFunction class

 

[Fluff]

In that case it probably couldn't find the value of "mytext" in A1:A69.
What sort of values does your combo hold?

 

[dmt32]

Thank you Dave for welcoming me in this group and thanks for the help. However, when I changed the code, this error prompt:

Run-time error '1004': Unable to get the Vlookup property of the WorksheetFunction class

Hi,
I have done a quick test with the updated code & works fine - so as Fluff suggests, likely that search value cannot be found.

Perhaps share with us some sample data & sure forum can resolve for you

Dave

 

[04011988]

In that case it probably couldn't find the value of "mytext" in A1:A69.
What sort of values does your combo hold?

Hi Fluff,

It includes LookUpList as per below. I created in the name manager "mytext" which equals to LookUpList!$A$2:$E$69".

[TABLE="width: 705"]
<colgroup><col width="77"><col width="182"><col width="141"><col width="148"><col width="157"></colgroup><tbody>[TR]
[TD="class: xl65, width: 77"]EMPLOYEE ID NUMBER[/TD]
[TD="class: xl65, width: 182"]EMPLOYEE NAME[/TD]
[TD="class: xl65, width: 141"]TEAM[/TD]
[TD="class: xl65, width: 148"]Team Leader[/TD]
[TD="class: xl65, width: 157"]OFFICE EMAIL ADDRESS[/TD]
[/TR]
</tbody>[/TABLE]


That code under ComboBox1 is the employee ID number and in which the 3 text boxes are dependent to ComboBox1.

 

[04011988]

Hi,
I have done a quick test with the updated code & works fine - so as Fluff suggests, likely that search value cannot be found.

Perhaps share with us some sample data & sure forum can resolve for you

Dave

Will try to explain a bit as not sure how to attach images here. Sorry.

 

[Fluff]

If they are numbers try

Dim mytext As Long


mytext = CLng(Me.ComboBox1.Text)

 

[04011988]

If they are numbers try

Dim mytext As Long


mytext = CLng(Me.ComboBox1.Text)

Now it says Run time error '13':

Type Mismatch

 

[Fluff]

Can you give some examples of the combobox values.