I'm trying to make a more functional search application to use on Excel. The idea is to be able to perform a search of a user-specified set of key words in cells that contain target text that can have up to 600 words. The application would locate all cells with at least one match and displays the entire content of the cell in a textbox (in this case textbox7 in UserForm1) and would highlight the matching words in orange colored text for quick identification of the context of the key words within the target text. For some reason I get an error message reading "Run-time error '438': Object doesn't support this property or method" and it highlights the line in my code reading: "With sh.TextBox". Below I've included a copy of the code with the section where this error is occurring. From what I can see it looks like the error is the result of the way I've dimensioned variable "sh" (i.e as msforms.TextBox) Ultimately what I need to achieve is something that will allow the use of "Characters" object which is a crucial part of this application because it locates the key words that have been found in the target text so that the font color can be changed to orange (and bold text). I would be very grateful if someone can point out what the issue is and how I can go about resolving it. Thanks in advance.
Private Sub CommandButton2_Click()
'code below displays all email items found with at least one word match
Dim Position As Long
Dim EmlCharacterLength As Long
Dim KeyWordCharacterLength As Long
Dim sh As msforms.TextBox
Set sh = UserForm1.TextBox7
SlotCount2 = 0
If RA1Counter < NmbrItemsFound Then
Do While ResultsArray1(1, RA1Counter) = ResultsArray1(1, RA1Counter + 1)
RA1Counter = RA1Counter + 1
Loop
EmailCntr = EmailCntr + 1
TextBox2.Value = EmailCntr & " of " & NmbrEmails
TextBox3.Value = CorroArray(5, ResultsArray1(1, RA1Counter))
TextBox4.Value = CorroArray(2, ResultsArray1(1, RA1Counter))
TextBox5.Value = CorroArray(3, ResultsArray1(1, RA1Counter))
TextBox6.Value = CorroArray(0, ResultsArray1(1, RA1Counter))
TextBox7.Value = CorroArray(4, ResultsArray1(1, RA1Counter))
EmlCharacterLength = Len(CorroArray(4, ResultsArray1(1, RA1Counter)))
Do While SlotCount2 < NbrKeyWords + 1
Position = 1
KeyWordCharacterLength = Len(SplitKeyWords(SlotCount2))
Do While Position < EmlCharacterLength
Position = InStr(Position, CorroArray(4, ResultsArray1(1, RA1Counter)), SplitKeyWords(SlotCount2), 1)
With sh.TextBox
.Characters(Start:=Position, Length:=KeyWordCharacterLength).Font.Bold = True
.Characters(Start:=Position, Length:=KeyWordCharacterLength).Font.Color = RGB(255, 102, 0)
End With
Position = Position + KeyWordCharacterLength
Loop
SlotCount2 = SlotCount2 + 1
Loop
RA1Counter = RA1Counter + 1
Else
RA1Counter = 1
EmailCntr = 0
End If
End Sub
