Basic Userform Search

jrp210

New Member
Joined
Jun 17, 2010
Messages
22
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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,346
.
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
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,922
Office Version
2019
Platform
Windows
Hi,
you could try something like following in your userform

Rich (BB 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
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,357
Office Version
365
Platform
Windows
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 = "[COLOR=#b22222]A[/COLOR]"
    
    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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,204
Office Version
2007
Platform
Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,102,542
Messages
5,487,473
Members
407,603
Latest member
jortronm

This Week's Hot Topics

Top