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

 

[Fluff]

With the code that you posted in post#14 you said you got "a new error", what was the error messgae?

 

[04011988]

oh sorry - that error now pertains to the dates.

when I place that code on what Dave said as position of the code.

 

[04011988]

Error is Run time 1004: Unable to get Vlookup property of the worksheetfunction class

 

[04011988]

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

Dave

Thank you Dave. I really appreciate your help. And prioritize granddad duties hahaha..mine can wait. hahahaha. thank you really.

 

[Fluff]

In that case the vlookup cannot find the combo value.
Is the lookup range on sheet codename sheet3?
If so, are the Col A values actually numbers, or text that looks like a number?

 

[04011988]

In that case the vlookup cannot find the combo value.
Is the lookup range on sheet codename sheet3?
If so, are the Col A values actually numbers, or text that looks like a number?

yes in sheet 3.

it is in numbers. but let me check it again.

 

[04011988]

yes in sheet 3.

it is in numbers. but let me check it again.

It is numbers.

 

[Fluff]

If the vlookup is searching the same sheet as the "Lookuplist" named range, run this

Sub chk()
   With Range("Lookuplist").Parent
      Debug.Print "|" & .Name & "|", .CodeName, [isnumber(a3)]
   End With
End Sub

& then copy the details from the Immediate window & paste them to the thread

 

[04011988]

If the vlookup is searching the same sheet as the "Lookuplist" named range, run this

Sub chk()
   With Range("Lookuplist").Parent
      Debug.Print "|" & .Name & "|", .CodeName, [isnumber(a3)]
   End With
End Sub

& then copy the details from the Immediate window & paste them to the thread

Error is Run time 1004: Unable to get Vlookup property of the worksheetfunction class

 

[Fluff]

You wont get that error from the code I asked you to run in post#28