(Solved) – VBA UserForm Search Button


I have a userform that has a combo box at the top which will activate specific sheets based on the selection in the combobox. I have a search that works but it will only search and display data from sheet1 but I would like it to search and display data based on the active sheet. I have tried to update the code multiple different ways and place activesheet in place of sheet1 but it always errors out. If someone could assist with the code please let me know and I would appreciate it.

 Private Sub cmdSearch_Click()

 Dim totRows As Long, i As Long

 totRows = Sheet1.range("A1").CurrentRegion.Rows.count

  If txtname.Text = "" Then
  MsgBox "Enter the name in the name block that you want to search"
  End If

  For i = 2 To totRows
   If Trim(Sheet1.Cells(i, 1)) <> Trim(txtname.Text) And i = totRows Then
    MsgBox "Name not found"
    End If

    If Trim(Sheet1.Cells(i, 1)) = Trim(txtname.Text) Then
    txtname.Text = Sheet1.Cells(i, 1)
    txtposition.Text = Sheet1.Cells(i, 2)
    txtassigned.Text = Sheet1.Cells(i, 3)
    cmbsection.Text = Sheet1.Cells(i, 4)
    txtdate.Text = Sheet1.Cells(i, 5)
    txtjoint.Text = Sheet1.Cells(i, 7)
    txtDAS.Text = Sheet1.Cells(i, 8)
    txtDEROS.Text = Sheet1.Cells(i, 9)
    txtDOR.Text = Sheet1.Cells(i, 10)
    txtTAFMSD.Text = Sheet1.Cells(i, 11)
    txtDOS.Text = Sheet1.Cells(i, 12)
    txtPAC.Text = Sheet1.Cells(i, 13)
    ComboTSC.Text = Sheet1.Cells(i, 14)
    txtTSC.Text = Sheet1.Cells(i, 15)
    txtAEF.Text = Sheet1.Cells(i, 16)
    txtPCC.Text = Sheet1.Cells(i, 17)
    txtcourses.Text = Sheet1.Cells(i, 18)
    txtseven.Text = Sheet1.Cells(i, 19)
    txtcle.Text = Sheet1.Cells(i, 20)
    txtnote.Text = Sheet1.Cells(i, 21)
    Exit For

    End If
    Next i

   End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *