• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Userform.Textbox: Get whole paragraph at cursor position

inddon

Member
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:

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
 

Attachments

  • Select whole paragraph at cursor position.xlsm
    24.1 KB · Views: 3
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:
SectionParagraph or SentenceIdentification
ASQL 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
BSQL 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
CSQL 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
DSQL Code 4:This is the SQL code for table selectSection D and E are 2 separate paragraphs/sentence.
Identiification: empty 2 lines between the 2 paragraph/sentence
ESQL Code 5: This is the SQL code for table employees_allThis is a paragraph/sentence.
Here there is no empty line after the paragraph/sentence


Thank you & regards,
Don
 

Attachments

  • Select whole paragraph at cursor position.xlsm
    25.6 KB · Views: 7
Same code as before just looking for a Double Character to split at

Code:
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
 
Same code as before just looking for a Double Character to split at

Code:
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.


75304

Thanks again and look forward to hearign from you.

Regards,
Don
 
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


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.


75451


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

Code:
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
 
I used:

Code:
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
 
I used:

Code:
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 :awesome::). It works perfect. Learned something new (Split function) from you.

Regards,
Don
 
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.


Code:
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
 
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
 
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.

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

75536

Regards,
Don
 
try:

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