Basic Userform Search
Results 1 to 8 of 8

Thread: Basic Userform Search
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2010
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Basic Userform Search

    Hello All,
    I have spent the last hour trying to find something useful to use to no avail. I have a userform with Textbox1 on, a search button and a cancel button. I basically want to type the date as dd/mm/yy in to the textbox and hit the search button for it then to find it on my worksheet named 2019 and select the same date.

    Thanks in advance for any help you can give.

    P.s I am having to post this from my phone as my company have blocked all internet access during certain hours. So unable ti provide a workbook example etc.

    Thanks again
    Joe

  2. #2
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    2,671
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Basic Userform Search

    .
    The following uses an InputBox as the search mechanism.

    Code:
    Option Explicit
    
    
    Sub findWord()
        Dim ws As Worksheet
        Dim findAword As Range
        Dim wordToFind As String
         
        Set ws = Sheet1
         
        wordToFind = InputBox("What word do you want to find?")
         
        If wordToFind = "" Then
            Exit Sub
        Else
            Set findAword = ws.Cells.Find(What:=wordToFind, LookAt:=xlPart, MatchCase:=False)
        End If
         
        If Not findAword Is Nothing Then
           findAword.Select
        Else
            MsgBox "Sorry, " & wordToFind & " was not found."
        End If
    End Sub

  3. #3
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    4,942
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Basic Userform Search

    Hi,
    you could try something like following in your userform

    Code:
    Private Sub CommandButton1_Click()
        Dim Search As Variant
        Dim FoundCell As Range, SearchRange As Range
        
    'search column - change as required
        Const SearchColumn As Variant = "A"
        
    'search sheet - change name as required
        Set SearchRange = Worksheets("2019").Columns(SearchColumn)
        
        Search = Me.TextBox1.Text
        
        If IsDate(Search) Then Search = DateValue(Search) Else Exit Sub
       
            Set FoundCell = SearchRange.Find(What:=Search, LookIn:=xlValues, LookAt:=xlWhole _
            , SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If Not FoundCell Is Nothing Then
                'record found
                'do stuff here
                MsgBox Search
                
                Else
        
            MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
        End If
    End Sub
    Change search column shown in red as required


    Dave

  4. #4
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,607
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Basic Userform Search

    another one for you
    - assumes dates are in column A

    Place code in Userform module (procedure names to match name given to each button)
    Code:
    Private Sub SearchButton_Click() 'search for date in textbox1
        Dim cel As Range, OldCel As Range, Last As Range, ws As Worksheet, msg As String
        Const col As String = "A"
        
        Set ws = Sheets("2019")
        Set Last = ws.Cells(ws.Rows.Count, col)
        Set cel = ws.Cells(1, col)
        Set OldCel = Last
        
        Do Until OldCel.Address = cel.Address
            Set OldCel = cel
            On Error Resume Next
            Set cel = ws.Range(cel, Last).Find(DateValue(TextBox1.Value))
            If Err.Number > 0 Then
                MsgBox "not found"
                Exit Sub
            End If
            msg = msg & vbCr & cel.Address(0, 0)
        Loop
    
        MsgBox msg, , cel
        
    End Sub
    
    Private Sub CancelButton_Click()
        TextBox1.Value = ""
    End Sub

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,320
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Basic Userform Search

    The date format in column A must also be "dd/mm/yy"
    Try this:

    Code:
    Private Sub CommandButton1_Click()
        Dim f As Range
        Set f = Range("A:A").Find(Format(CDate(TextBox1.Value), "dd/mm/yy"), LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then f.Select Else MsgBox "Does not exist"
    End Sub
    Last edited by DanteAmor; Jul 20th, 2019 at 10:59 PM.
    Regards Dante Amor

  6. #6
    New Member
    Join Date
    Jun 2010
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Basic Userform Search

    Thanks for all your replies, greatly appreciated.

  7. #7
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    4,942
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Basic Userform Search

    Quote Originally Posted by jrp210 View Post
    Thanks for all your replies, greatly appreciated.
    welcome - glad we were able to help

    Dave

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,320
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Basic Userform Search

    Quote Originally Posted by jrp210 View Post
    Thanks for all your replies, greatly appreciated.
    Im glad to help you. Thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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