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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top