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

04011988

New Member
Joined
Sep 23, 2018
Messages
26
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:
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
 
Last edited by a moderator:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Hi,
welcome to forum

try changing

Code:
Dim mytext As Long


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


to this

Code:
Dim mytext As Variant


mytext = Me.ComboBox1.Text

and see if solves the issue

Dave
 
Upvote 0
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
 
Upvote 0
In that case it probably couldn't find the value of "mytext" in A1:A69.
What sort of values does your combo hold?
 
Upvote 0
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
If they are numbers try
Code:
Dim mytext As Long


mytext = CLng(Me.ComboBox1.Text)
 
Upvote 0
Can you give some examples of the combobox values.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top