Linking a command button to a text box

[dpaton05]

I have a text box that is used as a search box, where a word is typed in and you can press a button to search for it. How do I link it so the procedure will run if I press enter after I type in the word?

 

[Logit]

Paste the following in a Routine Module :

Sub FindAll()

'PURPOSE: Find all cells containing a specified values
'SOURCE: www.TheSpreadsheetGuru.com

Dim fnd As String, FirstFound As String
Dim FoundCell As Range, rng As Range
Dim myRange As Range, LastCell As Range
Dim TextBox1 As TextBox

'What value do you want to find (must be in string form)?
  fnd = Sheets("Sheet1").TextBox1.Value     '<--- specify which sheet here

Set myRange = ActiveSheet.UsedRange
Set LastCell = myRange.Cells(myRange.Cells.Count)
Set FoundCell = myRange.Find(what:=fnd, after:=LastCell)

'Test to see if anything was found
  If Not FoundCell Is Nothing Then
    FirstFound = FoundCell.Address
  Else
    GoTo NothingFound
  End If

Set rng = FoundCell

'Loop until cycled through all unique finds
  Do Until FoundCell Is Nothing
    'Find next cell with fnd value
      Set FoundCell = myRange.FindNext(after:=FoundCell)
    
    'Add found cell to rng range variable
      Set rng = Union(rng, FoundCell)
    
    'Test to see if cycled through to first found cell
      If FoundCell.Address = FirstFound Then Exit Do
      
  Loop

'Select Cells Containing Find Value
  rng.Select
 
Exit Sub

'Error Handler
NothingFound:
  MsgBox "No values were found in this worksheet"

End Sub

Connect the command button to that macro. Place TextBox1 on your worksheet.

Enter search term in textbox and click button.

 

[dpaton05]

That seems to work well, thanks for that. How do I link the text box so it also runs the search procedure if I press enter after typing?

 

[Logit]

Paste this in the sheet level module :

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 13 Then
        FindAll
    End If
End Sub

 

[dpaton05]

Paste this in the sheet level module :

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 13 Then
        FindAll
    End If
End Sub

Thanks for that, it works well. The only problem is that if the text can't be found, it appears to go into a never ending loop of a message box that says the text can't be found.

 

[Logit]

I'm not certain what might be occurring there.

My copy of the macros (with the term to be found located on the bottom row of the sheet - over 1 million rows) locates the term in approx. 2.1 seconds.

If the term to be found is removed from the sheet, the macros displays "No values were found in this worksheet" in the same amount of time.