VBA to highlight textbox (1 Viewer)

vinzanitee

New member
Local time
Today, 11:08
Joined
Feb 24, 2021
Messages
4
Hi Team, desperately need your expertise. I have this code that will highlight the keyword result query. This time I want to highlight the textbox of the results instead of the text itself. Thank you in advance!

From:
Capture.JPG


to:
4.JPG


Code:
Public gPhoneFltr As String

Public Const FONT_COLOR = "#FFFFFF"
Public Const FONT_BGCOLOR = "#FF6600"


Public Const gBasicHTMLTemplate = "<div>{3}<font color={1} style=""BACKGROUND-COLOR:{2}"">{4}</font>{5}</div>"

Public Function getPhoneFltr() As String
    getPhoneFltr = gPhoneFltr
End Function

Public Function getBasicHTMLTemplate() As String
    getBasicHTMLTemplate = gBasicHTMLTemplate
End Function


Public Function formatMatch(ByRef oValue As Variant, ByRef oMatch As Variant, ByRef oTemplate As Variant) As String

    Dim sValue As String
    Dim sMatch As String
    Dim sTemplate As String
    Dim iPos As String
    
    Dim beforeMatch As String
    Dim onMatch As String
    Dim afterMatch As String
    
    sValue = Nz(oValue, "")
    sMatch = Nz(oMatch, "")
    sTemplate = Nz(oTemplate, "")
    formatMatch = sValue
    
    
    If Len(sValue) > 0 And Len(sMatch) > 0 And Len(sTemplate) > 0 Then
        iPos = InStr(1, sValue, sMatch, vbDatabaseCompare)

        If iPos > 0 Then
            iPos = iPos - 1
            formatMatch = sTemplate
            
            ' Color
            formatMatch = Replace(formatMatch, "{1}", FONT_COLOR)
            formatMatch = Replace(formatMatch, "{2}", FONT_BGCOLOR)

            
'            If sValue = sMatch Then
'                formatMatch = Replace(formatMatch, "{3}", "")
'                onMatch = Mid(sValue, 0, Len(sMatch))
'                formatMatch = Replace(formatMatch, "{4}", onMatch)
'                formatMatch = Replace(formatMatch, "{5}", "")
'            Else
            
                ' Before Match
                beforeMatch = Mid(sValue, 1, iPos)
                If iPos - 1 > 0 Then
                    formatMatch = Replace(formatMatch, "{3}", beforeMatch)         'Left(sValue, iPos))
                Else
                    formatMatch = Replace(formatMatch, "{3}", "")
                End If
                
                ' Match
                onMatch = Mid(sValue, iPos + 1, Len(sMatch))
                formatMatch = Replace(formatMatch, "{4}", onMatch)
                
                ' After Match
                afterMatch = Mid(sValue, iPos + 1 + Len(sMatch))
                If (iPos - 1) + Len(sMatch) < Len(sValue) Then
                    formatMatch = Replace(formatMatch, "{5}", afterMatch)
                Else
                    formatMatch = Replace(formatMatch, "{5}", "")
                End If
            End If
'        End If
    End If
    
End Function
 

Ranman256

Well-known member
Local time
Today, 07:08
Joined
Apr 9, 2015
Messages
3,891
in the textbox GOT FOCUS event:
txtbox.backcolor = vbYellow

in the textbox LOST FOCUS event:
txtbox.backcolor = vbWhite
 

vinzanitee

New member
Local time
Today, 11:08
Joined
Feb 24, 2021
Messages
4
in the textbox GOT FOCUS event:
txtbox.backcolor = vbYellow

in the textbox LOST FOCUS event:
txtbox.backcolor = vbWhite
thanks @Ranman256. that was not actually the option I have since I have multiple textboxes. so if you add an event to every textbox, the search result will not be accurate.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:08
Joined
May 21, 2018
Messages
5,121
I would do this in conditional formatting.
Make a function

Public Function IsMatch(frmName as string, ctrlName as string, Val as variant) as boolean
isMatch = (forms(frmName).controls(ctrlName).value = val)
end function

if this is a "contains" not an equal then change the = to some kind of instr

then you can use this anywhere in your database. Something like

Expressionis: IsMatch("someformName","thecontrolName",[FormName])
 

vinzanitee

New member
Local time
Today, 11:08
Joined
Feb 24, 2021
Messages
4
I would do this in conditional formatting.
Make a function

Public Function IsMatch(frmName as string, ctrlName as string, Val as variant) as boolean
isMatch = (forms(frmName).controls(ctrlName).value = val)
end function

if this is a "contains" not an equal then change the = to some kind of instr

then you can use this anywhere in your database. Something like

Expressionis: IsMatch("someformName","thecontrolName",[FormName])
thank you sir. is there a way that I can add on the code that I have right now? and not to create another logic or argument?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:08
Joined
May 21, 2018
Messages
5,121
Highlighting a single word like you have is far more involved since you have to use rich text and tags. To format a complete box is easy since you just change fore and back color. Sure you can just modify your code and do the same thing. IF this is not a continuos form then you can just very easily code to change fore and back color If continuous you will need either conditional formatting or this more complicated approach of rich text formatting with tags. For me I could do the continuous code in a few minutes. It would take me a while to figure out and code the rich text solution. I have to figure out the tags and the code is more involved. If it is a single form view then you can code this very quickly.
 

vinzanitee

New member
Local time
Today, 11:08
Joined
Feb 24, 2021
Messages
4
I have this code in all textboxes:

Code:
=formatMatch([Notes],getPhoneFltr(),getBasicHTMLTemplate())
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:08
Joined
Sep 21, 2011
Messages
8,524
Cross posted today at the same time
 

Users who are viewing this thread

Top Bottom