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

 

[04011988]

Can you give some examples of the combobox values.

ComboBox1 = Employee ID (e.g.35, 32 etc.) and from the lookuplist this is a1:a69) including the header

If I select the Employee ID number from combobox1, it will auto-populate the following:
textbox 1 = employee name (from the lookuplist this is b1:b69)
textbox 2 = team (from the lookuplist this is c1:c69)
textbox 3 = Leave approver (from the lookuplist this is d1:d69)

 

[Fluff]

You should not get a type mismatch error on this line

mytext = CLng(Me.ComboBox1.Text)

If you have numbers in the combobox.
How is the combo being populated?

 

[dmt32]

Now it says Run time error '13':

Type Mismatch

See if this idea resolves the issue

Dim mytext As Long
mytext = Val(Me.ComboBox1.Text)
If mytext = 0 Then Exit Sub

The VAl function is a little more forgiving than Clng as any errors it returns 0.
You will note that I added an additional line to exit the sub if 0 is returned.

As fluff asked, would help to understand how you are populating the combobox.

Dave

 

[04011988]

See if this idea resolves the issue

Dim mytext As Long
mytext = Val(Me.ComboBox1.Text)
If mytext = 0 Then Exit Sub

The VAl function is a little more forgiving than Clng as any errors it returns 0.
You will note that I added an additional line to exit the sub if 0 is returned.

As fluff asked, would help to understand how you are populating the combobox.

Dave

Hi Dave,

Still not working.

So first, I have rowsource - "LookUpList" added in the name manager = (A1:E69)

Column A = Employee ID (ComboBox1)
Column B = Employee Name (TextBox1)
Column C = Team (TextBox2)
Column D = Team Leader (TextBox3)

And I now used these codes but new error appeared, it highlighted the textbox1...:

Private Sub cmdEmpID_Change()

Sheet3.Activate

Dim mytext As Long

mytext = Val(Me.cmdEmpID.Text)

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)

If mytext = 0 Then Exit Sub

End Sub

 

[04011988]

But the thing is, the data I am entering in the userform still populates in my excel - but just those errors popping out. (e.g. I selected 1083 in the Employee ID)

Here is a sample of the populated data

[TABLE="width: 1349"]
<tbody>[TR]
[TD]Employee ID[/TD]
[TD]Employee Name[/TD]
[TD]Date Filed[/TD]
[TD]Team[/TD]
[TD]No.of Days Leave[/TD]
[TD]Leave Start Date[/TD]
[TD]Leave End Date[/TD]
[TD]Leave Type[/TD]
[TD]Reason[/TD]
[TD]Leave Approver[/TD]
[/TR]
[TR]
[TD]1083[/TD]
[TD]zzzz[/TD]
[TD]24/09/2018[/TD]
[TD]xxxx[/TD]
[TD]1[/TD]
[TD]21/09/2018[/TD]
[TD]21/09/2018[/TD]
[TD]Sick Leave[/TD]
[TD]afa[/TD]
[TD]xxxxx[/TD]
[/TR]
</tbody>[/TABLE]

 

[dmt32]

Hi,
do not have a lot of time today

Populating combobox using RowSource not an approach I would use but for now, try placing the line to exit sub exactly where I placed it & see if you still have issue.

Dim mytext As Long
mytext = Val(Me.ComboBox1.Text)
If mytext = 0 Then Exit Sub

'rest of code

Dave

 

[04011988]

Hi,
do not have a lot of time today

Populating combobox using RowSource not an approach I would use but for now, try placing the line to exit sub exactly where I placed it & see if you still have issue.

Dim mytext As Long
mytext = Val(Me.ComboBox1.Text)
If mytext = 0 Then Exit Sub

'rest of code

Dave

Hi Dave,

Thanks. But now, none has been populated on the textboxes.

 

[dmt32]

Hi Dave,

Thanks. But now, none has been populated on the textboxes.

ok - I am on granddad duties today & if Fluff has not responded with solution, will post further suggestions later.

Dave

 

[Fluff]

And I now used these codes but new error appeared, it highlighted the textbox1...:

What is the new error?

 

[04011988]

What is the new error?

If I put the code like this, none will populate it in the textboxes and this appears when I click save: Run time error 35787 - Can't set Value to NULL when checkbox property is False
Dim mytext As Long
mytext = Val(Me.ComboBox1.Text)


If mytext = 0 Then Exit Sub