excel - How to set textbox value to variable to use in match function VBA

Question

I want to let a user input a string into a text box on a user form. I then want to compare that value to a range in a table.

I try to set the textbox value to a variable and it tells me I an "Object is required".

Private Sub CommandButton1_Click()
Dim NwIns As TextBox
Dim tblm As Range
Dim c As TextBox

Set tblm = Worksheets("Main Committee Database").ListObjects("CredDB").ListColumns(3).DataBodyRange
UserForm1.TextBox1.SetFocus
Set c = UserForm1.TextBox1.Value

If IsNumeric(Application.WorksheetFunction.Match(c, tblm, 0)) = True Then

MsgBox "This Vendor Already Exists"

Else

question from:https://stackoverflow.com/questions/65904450/how-to-set-textbox-value-to-variable-to-use-in-match-function-vba

Answer 1

If you want C as a textbox you can't assign a string value to it.

Dim c as Control
Set c = UserForm1.Controls("TextBox1")

Or you could do

Dim c as string
c = UserForm1.TextBox1

But if you want to do anything to that textbox, like clear it after, it's best to just get the object itself instead of the value.

application.worksheetfunction.match will error if doesn't match. You could use error handling instead of your if statement but that isn't a great way to deal with it. A better way is to use .find and then check if it actually found anything.

All together:

Private Sub CommandButton1_Click()
Dim NwIns As TextBox
Dim tblm As Range
Dim c As Control
Dim fndrng As Range

Set tblm = Worksheets("Main Committee Database").ListObjects("CredDB").ListColumns(3).DataBodyRange
UserForm1.TextBox1.SetFocus

Set c = UserForm1.Controls("TextBox1")
Set fndrng = tblm.Find(c.Value, LookIn:=xlValues, lookat:=xlWhole) 'You may want to play with those parameters to meet your needs.

If Not fndrng Is Nothing Then
    MsgBox "This Vendor Already Exists"

Else