Userform.Textbox: Get whole paragraph at cursor position

[inddon]

Hello There,

I would like to know the VBA code to get the entire paragraph at current cursor position in a userform textbox.

The solution for my previous post to get the entire word at cursor postion was provided by @Hui. I tried to modify the code inthere, in vain. : Reference below:

Userform.Textbox: Get the cursor position's entire word

Hello There, I have a userform with the Textbox. I would like to know the VBA code to get the entire word where the cursor is placed. I have attached a sample workbook for your reference. Thank you & regards, Don

chandoo.org

Please find attached the sample workbook for your reference (includes the above 'entire word' solution)

Look forward to hearing from you.

Thank you & regards,
Don

 

[Hui]

What do you define as a paragraph or sentance ?

 

[inddon]

Hi Hui,

The textbox property is multi line and word wrap = TRUE. The identification criteria in the textbox could be an empty line/line break.

I have attached the below example in the updated sample workbook, for your reference.

for example:

Section	Paragraph or Sentence	Identification
A	SQL Code 1:This is the SQL code for table select_employees_all The cursor is on string 'select_employees_all', it should return: select_employees_all	Section A and B are 2 separate paragraphs. Identiification: is an empty 1 line between the 2 paragraphs
B	SQL Code 2:This is the SQL code for table select_employees_all The cursor is on string 'select_employees_all', it should return: select_employees_all	Section B and C are 2 separate paragraphs. Identiification: empty 3 lines between the 2 paragraphs
C	SQL Code 3:This is the SQL code for table select_employees_all The cursor is on string 'select_employees_all', it should return: select_employees_all	Section C and D are 2 separate paragraphs. Identiification: is an empty 1 line between the 2 paragraph/sentence
D	SQL Code 4:This is the SQL code for table select	Section D and E are 2 separate paragraphs/sentence. Identiification: empty 2 lines between the 2 paragraph/sentence
E	SQL Code 5: This is the SQL code for table employees_all	This is a paragraph/sentence. Here there is no empty line after the paragraph/sentence

Thank you & regards,
Don

 

[Hui]

Same code as before just looking for a Double Character to split at

Private Sub CommandButton2_Click()
Dim ans As String
Dim MyStr As String

MyStr = TextBoxParagraph.Text

MyStr = Replace(MyStr, Chr(10), Chr(234))
MyStr = Replace(MyStr, Chr(13), Chr(234))

Dim SplitStr As String
SplitStr = Chr(234) + Chr(234)
Debug.Print MyStr

Dim s As Variant
s = split(MyStr, SplitStr)

Dim comstr As String
comstr = ""

Dim i As Integer
For i = 0 To UBound(s)
   comstr = comstr + s(i)

   If Len(comstr) > TextBoxParagraph.SelStart Then
      ans = s(i)
      Exit For
   End If
  
   comstr = comstr + " "
Next i

a = MsgBox(ans)

End Sub

 

[inddon]

Same code as before just looking for a Double Character to split at

Private Sub CommandButton2_Click()
Dim ans As String
Dim MyStr As String

MyStr = TextBoxParagraph.Text

MyStr = Replace(MyStr, Chr(10), Chr(234))
MyStr = Replace(MyStr, Chr(13), Chr(234))

Dim SplitStr As String
SplitStr = Chr(234) + Chr(234)
Debug.Print MyStr

Dim s As Variant
s = split(MyStr, SplitStr)

Dim comstr As String
comstr = ""

Dim i As Integer
For i = 0 To UBound(s)
   comstr = comstr + s(i)

   If Len(comstr) > TextBoxParagraph.SelStart Then
      ans = s(i)
      Exit For
   End If

   comstr = comstr + " "
Next i

a = MsgBox(ans)

End Sub

Thank you @Hui for the VBA Code. I can use it for a different purpose.

The current solution displays the sentence of the cursor location. Whereas it should display the entire paragraph. The identification of a paragraph is a empty line between 2 paragraphs or a sentence.




Thanks again and look forward to hearign from you.

Regards,
Don

 

[Hui]

Don

You should be able to look at the logic of what is happening and see if you can work on a solution

Give it a go first

 

[inddon]

Don

You should be able to look at the logic of what is happening and see if you can work on a solution

Give it a go first

Yes Hui, I will give it a try and see if I can make it work.

Regards,
Don

 

[inddon]

Yes Hui, I will give it a try and see if I can make it work.

Regards,
Don

Hello @Hui

I tried to debug and understand your code and did the following changes to get the paragraph at the cursor position. It does give the results, but not how you it is on the Textbox, the enter/linefeed is missing. I am replacing all the chr(234) with " ". Somewhere with this it is not going good.

Could you please have a look at the changes and let me know the correction?

Thanks a lot.





The code below:
I added the following:
1. SplitStr = Chr(234) + Chr(234) + Chr(234) + Chr(234)
2. ans = Replace(ans, Chr(234), " ")

Private Sub CommandButton2_Click()
Dim ans As String
Dim MyStr As String

MyStr = TextBoxParagraph.Text
MyStr = Replace(MyStr, Chr(10), Chr(234)) 'replace all enters or next line feed with with êê
MyStr = Replace(MyStr, Chr(13), Chr(234)) 'replace enter with êê and make the entire content into one line

Dim SplitStr As String
'-------------------Change done here 1-------------------
SplitStr = Chr(234) + Chr(234) + Chr(234) + Chr(234)    'ê + ê.... = êêêê

Dim s As Variant
s = Split(MyStr, SplitStr)

Dim comstr As String
comstr = ""

Dim i As Integer
For i = 0 To UBound(s)
   comstr = comstr + s(i)
   If Len(comstr) > TextBoxParagraph.SelStart Then
      ans = s(i)
      '-------------------Change done here 2-------------------
      ans = Replace(ans, Chr(234), " ")    'replace all êêêê with " "
      Debug.Print ans
      Exit For
   End If
   comstr = comstr + " "
Next i
a = MsgBox(ans)
End Sub

Regards,
Don

 

[Hui]

I used:

Private Sub CommandButton2_Click()
Dim ans As String
Dim MyStr As String

MyStr = TextBoxParagraph.Text
x = 1
'MyStr = Replace(MyStr, Chr(10), Chr(234))
'MyStr = Replace(MyStr, Chr(13), Chr(234))

Dim SplitStr As String
SplitStr = Chr(13) + Chr(10) + Chr(13) + Chr(10)
Debug.Print MyStr

Dim s As Variant
s = Split(MyStr, SplitStr)

Dim comstr As String
comstr = ""

Dim i As Integer
For i = 0 To UBound(s)
   comstr = comstr + s(i)

   If Len(comstr) > TextBoxParagraph.SelStart Then
      ans = s(i)
      Exit For
   End If
  
   comstr = comstr + " "
Next i

a = MsgBox(ans)

End Sub

 

[inddon]

I used:

Private Sub CommandButton2_Click()
Dim ans As String
Dim MyStr As String

MyStr = TextBoxParagraph.Text
x = 1
'MyStr = Replace(MyStr, Chr(10), Chr(234))
'MyStr = Replace(MyStr, Chr(13), Chr(234))

Dim SplitStr As String
SplitStr = Chr(13) + Chr(10) + Chr(13) + Chr(10)
Debug.Print MyStr

Dim s As Variant
s = Split(MyStr, SplitStr)

Dim comstr As String
comstr = ""

Dim i As Integer
For i = 0 To UBound(s)
   comstr = comstr + s(i)

   If Len(comstr) > TextBoxParagraph.SelStart Then
      ans = s(i)
      Exit For
   End If
 
   comstr = comstr + " "
Next i

a = MsgBox(ans)

End Sub

Thank you @Hui . It works perfect. Learned something new (Split function) from you.

Regards,
Don

 

[inddon]

Hello @Hui

I am trying to include one last functionality: to highlight the paragraph.
I tried various options but cannot figure it out and therefore the request for correction (last section in the below code)
Could you please help me out?

Thank you once again and look forward to hearing from you.

Private Sub CommandButton2_Click()
Dim ans As String
Dim MyStr As String

MyStr = TextBoxParagraph.Text
x = 1
'MyStr = Replace(MyStr, Chr(10), Chr(234))
'MyStr = Replace(MyStr, Chr(13), Chr(234))

Dim SplitStr As String
SplitStr = Chr(13) + Chr(10) + Chr(13) + Chr(10)
Debug.Print MyStr

Dim s As Variant
s = Split(MyStr, SplitStr)

Dim comstr As String
comstr = ""

Dim i As Integer
For i = 0 To UBound(s)
   comstr = comstr + s(i)

   If Len(comstr) > TextBoxParagraph.SelStart Then
      ans = s(i)
      Exit For
   End If
 
   comstr = comstr + " "
Next i

'---------------------------------------------------
'Highlight the current cursor paragraph
'---------------------------------------------------
With TextBoxParagraph
      .SetFocus
      .SelStart = InStr(comstr, ans) - 1
      .SelLength = Len(ans)
      .SetFocus
   Debug.Print "comstr: " & comstr & vbNewLine & _
             ".SelStart: " & .SelStart & vbNewLine & _
             ".SelLength: " & .SelLength

a = MsgBox(ans)

End Sub

Regards,
Don

 

[Hui]

Don

As far as I know, you can't control the formatting of a VBA TextBox's text, other than as a whole.

You can use other VBA Controls to hold the text, that will allow that functionality
eg: a Rich Text or HTML Box

 

[inddon]

Don

As far as I know, you can't control the formatting of a VBA TextBox's text, other than as a whole.

You can use other VBA Controls to hold the text, that will allow that functionality
eg: a Rich Text or HTML Box

Hi @Hui

Thank you for your reply.

Your last code works perfect. It displays the cursor position's whole paragraph in the Msgbox.
I would like the result that is displayed in the MsgBox(ans), gets selected as seen in the attached snapshot. There is no formatting involved.

I did the following, but the text does not get selectd correctly.

With TextBoxParagraph
      .SetFocus
      .SelStart = InStr(comstr, ans) - 1
      .SelLength = Len(ans)
      .SetFocus
End With

Regards,
Don

 

[Hui]

try:

'---------------------------------------------------
'Highlight the current cursor paragraph
'---------------------------------------------------
With TextBoxParagraph
      .SetFocus
      .SelStart = InStr(comstr, ans) - i - 1
      .SelLength = Len(ans) - i - 1
      .SetFocus
  
End With