Match function doesn't work

[AveryHilditch]

I am developing an app where I need to get the content of column 1 in a row selected by the user. When I run the app, a list box is populated with the contents of a table. I am currently working on an Update data function. Double clicking a row successfully populates the fields in a form. The first image shows some of the form and the second the list box.

If I double click a row, the data in the selected row is copied to the form. If I then click the Update button I have placed on the form, the contents of the form are copied back to the selected row in the list box and data table.

I am using the following code:

Private Sub UpdateBtn_Click()
Dim Selected_Row As Long
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")
Selected_Row = Application.WorksheetFunction.Match(Me.TextBox1.Value, sh.Range("A:A"), 0)

'... More code

End Sub

When I run the code, I get runtime error 1004 "Unable to get the Match property of the WorksheetFunction class"

I have used exactly the same function without any issues in other apps but not in this one. I am tearing my hair out trying to work out why it isn't working

Thank you

 

[Fluff]

Hi & welcome to MrExcel.
What is in textbox1?

 

[AveryHilditch]

I am developing an app where I need to get the content of column 1 in a row selected by the user. When I run the app, a list box is populated with the contents of a table. I am currently working on an Update data function. Double clicking a row successfully populates the fields in a form. The first image shows some of the form and the second the list box.

View attachment 49865

View attachment 49866

If I double click a row, the data in the selected row is copied to the form. If I then click the Update button I have placed on the form, the contents of the form are copied back to the selected row in the list box and data table.

I am using the following code:

Private Sub UpdateBtn_Click()
Dim Selected_Row As Long
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")
Selected_Row = Application.WorksheetFunction.Match(Me.TextBox1.Value, sh.Range("A:A"), 0)

'... More code

End Sub

When I run the code, I get runtime error 1004 "Unable to get the Match property of the WorksheetFunction class"

I have used exactly the same function without any issues in other apps but not in this one. I am tearing my hair out trying to work out why it isn't working

Thank you

Oops. I should have said that TextBox2 contains the number of the selected row.

I get this error regardless of which data row I select

 

[Fluff]

But your code is trying to match textbox1 not textbox2

 

[AveryHilditch]

But your code is trying to match textbox1 not textbox2

I really shouldn't be trying to work in the early hours! TextBox1 contains the number of the selected row.

I made TextBox1 visible to show you that I am actually getting the row number in that field

If it will help, this is the code executed when I double click the List Box

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Me.TextBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
Me.VerifiedCB.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
Me.MembershipCB.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 2)
Me.PaidCB.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 3)
End Sub

 

[Fluff]

Ok, try

Selected_Row = Application.WorksheetFunction.Match(Val(Me.TextBox1.Value), sh.Range("A:A"), 0)

 

[AveryHilditch]

Ok, try

Selected_Row = Application.WorksheetFunction.Match(Val(Me.TextBox1.Value), sh.Range("A:A"), 0)

Thank you. That worked. I really appreciate your help.

 

[Fluff]

You're welcome & thanks for the feedback.