+ Reply to Thread
Results 1 to 5 of 5

VBA UserForm Search Button

Hybrid View

  1. 03-19-2020, 02:27 AM #1
    pumashoes
    pumashoes is offline
    Registered User
    Join Date
    03-19-2020
    Location
    South Korea
    MS-Off Ver
    excel 2016
    Posts
    6

    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.

    The code I have for the search is here:

    [Private Sub cmdSearch_Click()


    Dim totRows As Long, i As Long

    totRows = ActiveSheet.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(ActiveSheet.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]
    Reply With QuoteRegister To Reply
  2. 03-19-2020, 04:47 AM #2
    bakerman2
    bakerman2 is offline
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,518

    Re: VBA UserForm Search Button

    For starters.

    Private Sub cmdSearch_Click()

Dim wRow

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

With ActiveSheet
    wRow = Application.Match(txtname.Text, .Columns(1), 0)
    If Not IsError(wRow) Then
        txtname.Text = .Cells(wRow, 1)
        txtposition.Text = .Cells(wRow, 2)
        txtassigned.Text = .Cells(wRow, 3)
        cmbsection.Text = .Cells(wRow, 4)
        txtdate.Text = .Cells(wRow, 5)
        txtjoint.Text = .Cells(wRow, 7)
        txtDAS.Text = .Cells(wRow, 8)
        txtDEROS.Text = .Cells(wRow, 9)
        txtDOR.Text = .Cells(wRow, 10)
        txtTAFMSD.Text = .Cells(wRow, 11)
        txtDOS.Text = .Cells(wRow, 12)
        txtPAC.Text = .Cells(wRow, 13)
        ComboTSC.Text = .Cells(wRow, 14)
        txtTSC.Text = .Cells(wRow, 15)
        txtAEF.Text = .Cells(wRow, 16)
        txtPCC.Text = .Cells(wRow, 17)
        txtcourses.Text = .Cells(wRow, 18)
        txtseven.Text = .Cells(wRow, 19)
        txtcle.Text = .Cells(wRow, 20)
        txtnote.Text = .Cells(wRow, 21)
    Else
        MsgBox "Name not found"
    End If
End With

End Sub
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
    Reply With QuoteRegister To Reply
  3. 03-19-2020, 05:16 PM #3
    pumashoes
    pumashoes is offline
    Registered User
    Join Date
    03-19-2020
    Location
    South Korea
    MS-Off Ver
    excel 2016
    Posts
    6

    Re: VBA UserForm Search Button

    This is awesome! Thank you for the help.
    Reply With QuoteRegister To Reply
  4. 03-19-2020, 09:19 PM #4
    bakerman2
    bakerman2 is offline
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,518

    Re: VBA UserForm Search Button

    You're welcome.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Reply With QuoteRegister To Reply
  5. 08-03-2021, 03:54 PM #5
    vesummers
    vesummers is offline
    Registered User
    Join Date
    08-03-2021
    Location
    New York
    MS-Off Ver
    365
    Posts
    1

    Re: VBA UserForm Search Button

    I know this thread was posted quite a while ago but it helped me immensely today. Thank you!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Userform Search Button and number manipulation.
    By Ph0enix in forum Excel General
    Replies: 1
    Last Post: 05-25-2015, 05:00 AM
  2. [SOLVED] userform search button remove search only when i type
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-08-2014, 02:32 AM
  3. Save Button and Search On Userform faults
    By flyinghigher2011 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-22-2013, 12:49 PM
  4. [SOLVED] Search button on an USERFORM
    By alopecito in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-25-2012, 09:34 AM
  5. Excel Userform - Button to search multiple columns
    By Nu2Java in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-09-2012, 09:54 PM
  6. [SOLVED] Search button in userform
    By Ray316 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-07-2011, 09:10 AM
  7. Search Button On Userform
    By markrennolds in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-03-2010, 04:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1