CODEWITHSUNDEEP
excelvba
cjvdg
cjvdg

Reputation: 533

VBA Excel - Underline specific text

I just want to ask if it is possible for a specific text to have an underline? As you can see in the codes below, I want the fname, stud and grd to be underlined when it shows in the Sheet2 sheet. The codes are located at Worksheet_Activate of Sheet2.

    Set ws = ActiveWorkbook.Worksheets("Sheet1")
    Lastrow = ws.Cells(Rows.Count, "C").End(xlUp).Row
        
    For r = 9 To Lastrow
        If ws.Cells(r, 3) = CStr(ThisWorkbook.Sheets("Sheet3").Range("K11").value) And ws.Cells(r, 12).value = 1 Then
                            
            fname = ws.Cells(r, 4).value
            stud = ws.Cells(r, 3).value
            grd = ws.Cells(r, 5).value + 1
            
            Text1 = "My name is "
            Text2 = " , my student ID is "
            Text3 = " and I'm grade "
        End If
            
    Next r
    
    With ThisWorkbook.Sheets("Sheet2")
        .Range("C72").value = Text1 & fname & Text2 & LRN & Text3 & grd & Text4
    End With

I tried using the .Characters(17, 13).Font.Underline = True, but I don't think I can use the 17,13 or any number since the value of fname, stud, and grd has a different length size.

Upvotes: 3

Views: 3596

Answers (2)

user3259118
user3259118

Reputation:

@Stax pointed you in the right direction, and it looks like you're just about there with a solution. I would further add storing the length of the fname, stud and grd strings in short variable names to minimise your code.

The relevant snippet would be (I'll let you work out how to do your loop etc.)

a = Len(fname)
b = Len(stud)
c = Len(grd)

With Sheet2.Range("C72")
    .Value = Text1 & fname & Text2 & stud & Text3 & grd
    .Characters(Start:=12, Length:=a).Font.Underline = True
    .Characters(Start:=36 + a, Length:=b).Font.Underline = True
    .Characters(Start:=51 + a + b, Length:=c).Font.Underline = True
End With

Upvotes: 2

DerinNehir
DerinNehir

Reputation: 56

I added a way to underline required parts of your text. It first concatenates your text, then applies underlying on needed words only. You'll have to correct your algo (what is to be done line per line, "C72" etc)...

Sub WordToUnderlined(ByRef pRange As Range, ByVal pWord As String, _
    ByRef pPos As Integer)
    Dim pos As Integer
    pos = InStr(pPos, pRange.Value, pWord, vbTextCompare)
    If pos > 0 Then
        With pRange.Characters(pos, Len(pWord))
            .Font.Underline = True
        End With
        WordToUnderlined pRange, pWord, pos + 1
    End If
End Sub

Sub TestMe()
    Set ws = ActiveWorkbook.Worksheets("Sheet1")
    LastRow = ws.Cells(Rows.Count, "C").End(xlUp).Row
        
    For R = 9 To LastRow
        If ws.Cells(R, 3) = CStr(ThisWorkbook.Sheets("Sheet3").Range("K11").Value) And ws.Cells(R, 12).Value = 1 Then
                            
            fname = ws.Cells(R, 4).Value
            stud = ws.Cells(R, 3).Value
            grd = ws.Cells(R, 5).Value + 1
            
            Text1 = "My name is "
            Text2 = " , my student ID is "
            Text3 = " and I'm grade "
        End If
        With ThisWorkbook.Sheets("Sheet2")
            .Range("C72").Value = Text1 & fname & Text2 & stud & Text3 & grd & Text4
            WordToUnderlined .Range("C72"), fname, 1
            WordToUnderlined .Range("C72"), stud, 1
            WordToUnderlined .Range("C72"), grd, 1
        End With
    Next R
    
End Sub

Upvotes: 0

Related Questions