Seaching an excel sheet

  • Thread starter Thread starter pcorcele
  • Start date Start date
P

pcorcele

I currently am using the following code to search my ecel sheet
Dim Prompt As String
Dim UserResp As String
Dim UR As Single



Dim i As Integer
Dim MyText As String, List, myTotal As Double
Dim Cell As Range
Set cmt = ActiveSheet.Comments
Set rng21 = Range("target")
Selection.SpecialCells(xlCellTypeComments).Select

For Each Cell In Selection
Cell.Select
myTotal = 0
MyText = UCase$(Cell.Comment.Text)

If InStr(MyText, UCase$(rng21)) Then

Cells(Cell.Row, 15).Value = rng21
Cells(Cell.Row, 16).Value = Cell.Address


End If

Next Cell
I would like to be able to input the search data in a TEXTbox and do
the same search.
Can anyone help with this
Thanks
and Happy New year
 
pcorcele said:
I currently am using the following code to search my ecel sheet [snip]
I would like to be able to input the search data in a TEXTbox and do
the same search.
Can anyone help with this
Click to expand...

Put this in the sheet's class module and change "TextBox1" to the name of the
textbox:
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
If 13 = KeyCode Then
Dim UserResp As String
Dim MyText As String
Dim Cell As Range

UserResp = TextBox1.Text

Selection.SpecialCells(xlCellTypeComments).Select

For Each Cell In Selection
MyText = UCase$(Cell.Comment.Text)
If InStr(MyText, UCase$(UserResp)) Then
Cells(Cell.Row, 15).Value = UserResp
Cells(Cell.Row, 16).Value = Cell.Address
End If
Next Cell
End If
End Sub

(This is mostly just your code.)

If you want the textbox cleared after every search, add this immediately
before "End If":
TextBox1.Text = ""
 
Back
Top