MS Excel - Text Hover - resize hover box to fit definition with maximum width of 50 characters

Refer to previous question https://www.experts-exchange.com/questions/26793292/MS-Excel-Text-Hover-resize-hover-box-to-fit-definition.html

Question:
I have received a near perfect fit solution from SiddharthRout for the question below but need to ask if the hover box can be set to ensure all the text can be viewed.

THe solution does dynamically fit the text but does so in one single line.

Can the text box be dynamically sized to fit the text with a maximum character width of 50 characters.

Any help appreciated.

Initial Question

I have a worksheet were C5 has a validation list of award classification (Shop Assistant, Visual Merchandiser, Checkout Operator, Meat Wrapper, Department Manager etc)

In the data set, each of the associated list elements (Shop Assistant, Visual Merchandiser, Checkout Operator, Meat Wrapper, Department Manager etc) has a dependant definition
e.g:
Y1 = Shop assistant      
Z1 = Shop assistants includes demonstrators, employees driving a forklift or using mechanical equipment as required, ticket writers, reserve stock hands, employees delivering goods by bicycle and shelf fillers.

With the Shop Assistant selected from the validation list in C5, i want to be able to hover over C5 and have a pop-up box with the definition in Z1 displayed.

Does Excel have the capacity to do this and if so is VBA or is there another approach

Sid’s response
Dim strComment As String
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C5")) Is Nothing Then
        Application.EnableEvents = False
        lastRow = ActiveSheet.Range("Y" & Rows.Count).End(xlUp).Row
       
        For i = 1 To lastRow
            If Range("Y" & i).Value = Range("C5").Value Then
                strComment = Range("Z" & i).Value
                Exit For
            End If
        Next i
       
        If Len(Trim(strComment)) = 0 Then
            Range("C5").ClearComments
        Else
            With Range("C5")
                .ClearComments
                .AddComment
                .Comment.Visible = False
                .Comment.Text Text:=strComment
            End With
            Range("C5").Comment.Shape.TextFrame.AutoSize = True
            strComment = ""
        End If
       
        Application.EnableEvents = True
    End If
End Sub