CODEWITHSUNDEEP
vbaformattingrichtextboxuserform
Spero
Spero

Reputation: 1

Is it possible to highlight specific text in an InkEdit box used for search results?

This is the code I am using:

 If matchFound Then
        results = results & row.Cells(1, 1).Value & " | " & _
                  row.Cells(1, 2).Value & " | " & _
                  row.Cells(1, 3).Value & " | " & _
                  "se" & row.Cells(1, 4).Value & " | " & _
                  "ep" & row.Cells(1, 5).Value & " | " & _
                  row.Cells(1, 6).Value & " | " & _
                  row.Cells(1, 7).Value & " | " & _
                  row.Cells(1, 8).Value & " | " & _
                  row.Cells(1, 9).Value & vbCrLf & String(284, "-") & vbCrLf
        resultCount = resultCount + 1
    End If

This provides the results of the search and places a bar (|) between each parameter. This code works well, but when there are a dozen results of varying lengths, it can be confusing and hard to decipher. My idea is to highlight every alternate parameter so that it can be clearly seen that the title or the artist or the year, etc can be distinguished.

I tried adding "\b" and "\b0", but what this ended up doing is including the characters in the result box. I am using an InkEdit box, as it appears that the RichTextBox is no longer available, at least for me. The InkEdit box gives me scrolling and formatting capabilities, rather than a simple textbox. I don't want to recreate the code as something different, I just want to incorporate bolding or highlighting in alternate text. That would be in cell(1,1), (1,3), (1,5), (1,7) and (1,9). I am aware of "cells(1,1).font.bold = True", but I'm not sure where I can fit that in without some error occurring.

Upvotes: 0

Views: 30

Answers (1)

Tim Williams
Tim Williams

Reputation: 166755

Here's a very basic example of using padding and a fixed-width font to create a plain text table with aligned values:

Private Sub UserForm_Activate()
    Dim txt As String
    txt = RangeToTable(ActiveSheet.Range("A1:H20"))
    Me.InkEdit1.Text = txt
    Me.InkEdit1.Font = "Courier"
End Sub

Private Function RangeToTable(rng As Range) As String
    Dim txt As String, r As Long, c As Long, v, i As Long
    Dim arr, nC As Long, nR As Long, maxLens() As Long
    
    arr = rng.Value
    nR = UBound(arr, 1)     '# of rows
    nC = UBound(arr, 2)     '# of columns
    ReDim maxLens(1 To nC)  'for max. length in each column
    
    For c = 1 To nC    'start by finding max length value for each column
        For r = 1 To nR
            v = Len(arr(r, c))
            If v > maxLens(c) Then maxLens(c) = v
        Next r
    Next c
    
    txt = ""
    For r = 1 To nR   'build the text with the appropriate space padding
        txt = txt & "|"
        For c = 1 To nC
            v = arr(r, c)
            i = maxLens(c) + 1 'add a little padding
            txt = txt & Left(v & String(i, " "), i) & "|"
        Next c
        txt = txt & vbLf
    Next r
    RangeToTable = txt
End Function

Upvotes: 0

Related Questions