• 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

inddon

Member
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

Hui

Excel Ninja
Staff member
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
 

inddon

Member
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
 

Hui

Excel Ninja
Staff member
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

Member
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
 

Hui

Excel Ninja
Staff member
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
 
Top