VBA - Row index based on a TextBox

[justinp]

I'm trying to get a row number (row index) based on a textbox.

I can get the row number with a combobox, code below:

intRowNumber = ComboBox1.ListIndex + 2

<STYLE>.alt2 font {font: 11px monospace !important;color: #333 !important;}</STYLE><!-- END TEMPLATE: bbcode_code -->I can't seem to get it to work with a textbox. I've tried ListIndex and TabIndex, but no luck.

Any ideas?

 

[Mike Blackman]

Does the TextBox have a dropdown then? If so what range is the TextBox reading?

 

[justinp]

Hi Mickey B,

I basically have 2 options where I can search for records, a textbox and a dropdown (combobox).

I've got the comobox (list) working, but i can't seem to retrieve the row number for the textbox based on a command button.

Listindex does not work for textboxes, there must be a similar command for textboxes?

 

[Mike Blackman]

What values are going into the Textbox? Does it have a rowsource or something or is it open text?

 

[justinp]

its open text, but this textbox (textbox6) is based on a specific column. with a match in the data that is input a row number must be retrieved. With the row number i can populate the rest of the form with the relevant data. I just can't seem to get the correct row number.

Any ideas?

 

[Mike Blackman]

Something like this maybe?

Function FindRow() As Long

    On Error Resume Next
    
    FindRow = Application.Match(myForm.TextBox1.Value, Sheets("MySheet").Columns("A"), 0)
    If Err.Number <> 0 Then FindRow = 0

End Function

 

[justinp]

Thanks Mickey.

Is there anyway you could find a row number (index) within a submit function? Submit function below:

Private Sub cmdSearch_Click()
 
 '  intRowNumber = ComboBox1.ListIndex + 2
    
   intRowNumber = TextBox6.Value        
   txtAddr.Text = intRowNumber
 
  '  If intRowNumber >= 1 Then
  '      With Sheets("Sheet1").Cells(intRowNumber, 1)
  '          'TextBox1.Text = .Value
  '          txtAddr.Text = intRowNumber
  '          TextBox1.Text = .Offset(0, 1).Value
  '          TextBox2.Text = .Offset(0, 2).Value
  '          TextBox3.Text = .Offset(0, 3).Value
  '          TextBox4.Text = .Offset(0, 4).Value
  '          Label9.Caption = .Offset(0, 0).Value
  '          Label10.Caption = .Offset(0, 6).Value
  '          Label11.Caption = .Offset(0, 7).Value
  '      End With
  '  Else
  '      TextBox1.Text = "No match"
  '  End If

End Sub