title: Excel VBA - get double-clicked word in userform multiline textbox
body: <p><strong>The Task</strong>: my goal is to extract the highlighted word from a multi-line <code>TextBox</code> in a <code>UserForm</code> after a double-click. </p>
  
  <p><strong>Used Properties</strong>: Whereas it's absolutely no problem to highlight a given string position via the <code>TextBox</code> properties <code>.SelStart</code> and <code>.SelLength</code>, it isn't as easy the other way round: a users <code>DblClick</code> highlights a whole word string, but Excel doesn't reset the <code>.SelStart</code> value at the starting position of the highlighted text as one could assume, the <code>.SelStart</code> value remains there where the user double-clicks. </p>
  
  <p><strong>My Question</strong>: is there any possibility to catch the highlighted text starting position directly as set by the application? </p>
  
  <p><strong>My work around</strong>: 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):</p>
  
  <pre><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 &gt; 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 &lt; 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 &gt; 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
  </code></pre>
  
  <hr>
  
  <blockquote>
    <p><strong>Additional explanations to found solution in UserForm code module (thx @Rory)</strong></p>
  </blockquote>
  
  <p>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:</p>
  
  <ol>
  <li>As the textbox position properties <code>SelStart</code> and <code>SelLength</code> aren't set yet in the <code>DblClick</code> event, 
   it's necessary to assign <code>True</code> to a boolean variable/marker (<code>bCheck</code>).</li>
  <li>Use the <code>MouseUp</code> event to get the final position properties after checking for <code>bCheck</code>.</li>
  <li><p>In order to count correctly, it'll be necessary to remove e.g. <code>vbLf</code> within the pair of <strong>carriage returns</strong> <code>Chr(13)</code> (=<code>vbCr</code>) and <strong>line feeds</strong> <code>Chr(10)</code> (=<code>vbLf</code>) on MS systems.  </p>
  
  <p><strong>Caveat:</strong> <em>Note that AFAIK Mac systems use only line feeds <code>Chr(10)</code> as ending sign, so IMO you can omit replacing in this case</em>.</p></li>
  </ol>
  
  <p><strong>Final Code</strong></p>
  
  <pre><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
  </code></pre>