Excel VBA - get double-clicked word in userform multiline textbox

Question

The Task: my goal is to extract the highlighted word from a multi-line TextBox in a UserForm after a double-click.

Used Properties: Whereas it's absolutely no problem to highlight a given string position via the TextBox properties .SelStart and .SelLength, it isn't as easy the other way round: a users DblClick highlights a whole word string, but Excel doesn't reset the .SelStart value at the starting position of the highlighted text as one could assume, the .SelStart value remains there where the user double-clicks.

My Question: is there any possibility to catch the highlighted text starting position directly as set by the application?

My work around: I will demonstrate a very simple work around to reconstruct the high-lighted word just by checking the following and preceding e.g. 20 letters right and left to the actual clicking position (of course, one could use regex as well and refine the example code):

Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim sTxt   As String, sSel As String       ' edited due to comment below
Dim selPos As Long, i As Long, i2 As Long  ' "
TextBox1.SetFocus  
' this is the User's DblClick Position, 
' but not the starting Position of the highlighted Word 
' after DblClick             
selPos = TextBox1.SelStart
sTxt = Replace(Replace(TextBox1.Text, vbCrLf, vbLf), "\", ".")
If TextBox1.SelLength > 0 Then
    sSel = TextBox1.SelText
Else
    sSel = Mid(sTxt, selPos + 1, 5)
    ' check the preceding 20 letters
    i = selPos
    For i = selPos To (selPos - 20) Step -1
        If i < 0 Then Exit For
        Select Case Left(Mid(sTxt, i + 1), 1)
          Case " ", vbLf, ":", ".", "?", """", "'", "(", ")"
             sSel = Mid(sTxt, i + 2, selPos - i)
             Exit For  
        End Select
    Next i
    ' check the following 20 letters
    i2 = selPos
    For i2 = selPos To (selPos + 20)
        If i2 > Len(sTxt) Then Exit For
        Select Case Left(Mid(sTxt, i2 + 1), 1)
          Case " ", vbLf, ":", ".", "?", """", "'", ")", "("
             sSel = Replace(Mid(sTxt, i + 2, i2 - i - IIf(i = i2, 0, 1)), vbLf, "")
             Exit For  
        End Select
    Next i2
End If
' Show the highlighted word
Me.Label1.Text = sSel

End Sub

---

Additional explanations to found solution in UserForm code module (thx @Rory)

In order to actually get the double-clicked highlighted string from a multi-line textbox, you'll need three steps to solve the timing problem:

1. As the textbox position properties SelStart and SelLength aren't set yet in the DblClick event, it's necessary to assign True to a boolean variable/marker (bCheck).
2. Use the MouseUp event to get the final position properties after checking for bCheck.

3. In order to count correctly, it'll be necessary to remove e.g. vbLf within the pair of carriage returns Chr(13) (=vbCr) and line feeds Chr(10) (=vbLf) on MS systems.

   Caveat: Note that AFAIK Mac systems use only line feeds Chr(10) as ending sign, so IMO you can omit replacing in this case.

Final Code

Option Explicit
Private bCheck As Boolean

' [1] assign True to boolean variable
Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    bCheck = True       ' set marker to True
End Sub

Private Sub TextBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If bCheck Then
        bCheck = False  ' reset marker to False
      ' [2][3] extract the highlighted doubleclicked word from multi-line textbox 
        MsgBox Trim(Mid(Replace(Me.TextBox1.Text, vbLf, ""), Me.TextBox1.SelStart + 1, Me.TextBox1.SelLength))
    End If
End Sub

Answer 1

I think it's a timing issue. It seems to work if you use a flag variable and the MouseUp event in conjunction with the DblClick event:

Private bCheck As Boolean

Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    bCheck = True
End Sub

Private Sub TextBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If bCheck Then
        bCheck = False
        MsgBox Me.TextBox1.SelStart & "; " & Me.TextBox1.SelLength
    End If
End Sub