Entered Text On UserForm Not Filling CC On Document

[HTSCF Fareha]

I'm trying to produce what ought to be the simplest of forms. There is a UserForm with one textbox (at the moment) that should populate the CC named PPN1 on the document, but will be formatted so that everything will be in proper case (Capital letter at the start of each sentence and all the other words in lower case).

I cannot tell if the proper case is working as the entered text will not even populate the document.

It must be something so simple that I just cannot see it. :banghead:

The necessity of the form is that the source text will be copied / pasted from multiple sources, some of which will be all upper case, some all lower case and crazily, some of a complete mixture.

[HTSCF Fareha]

Okay, so I have solved my UserForm not placing the text on the page.

Missed the second line as per below in bold.


For Each occ In oDoc.ContentControls
Set oRng = occ.Range

Just struggling to get the text to format correctly.

This line makes every word capitalize.


oRng.Text = StrConv(.txtPPN1.Text, vbProperCase)

[gmayor]

Fixed! See attached.

[HTSCF Fareha]

That, Graham, is absolutely brilliant!! Many, many thanks!!!

I followed the use of
wdTitleSentence and can see that this preps things.

But the modPopupMenu is just something else! You sir, are a total VBA guru!! :bow:

I love the way that it will keep any alterations made within the UserForm, but will still tidy things if the entered text contains random uppercase/lowercase letters for example, if they happen in the wrong place. The only thing that one needs to get in their head is the lack of paragraphs on the UserForm and that if you produce one yourself, this will translate as a double paragraphed entry on the document. This is soon adopted and is not an issue.

Are there any limitations that you are aware of?

Thanks again!
Steve

[gmayor]

The pop-up is a documented code from the internet. I take no credit for producing it, though I did amend it for 64 bit compatibility.
Use an appropriate style to add spacing between paragraphs. If you are troubled by double paragraph breaks you could replace them e.g.

oRng.Text = .txtPPN1.Text
oRng.Text = Replace(oRng.Text, Chr(13) & Chr(13), Chr(13))

[HTSCF Fareha]

Absolutely spot on, thank you.

[HTSCF Fareha]

It's really strange that when performing your own testing at home, everything seems to work okay. But take it into a work environment and the results are less predicatable. Totally frustrating!

What's worse is that it is difficult to establish why there are differing results.

Looking acrosss the internet, I came across this link

https://www.vbforums.com/showthread.php?669352-RESOLVED-Sentence-case-in-Textbox

I am not sure how the suggestions might work if they could be adapted or whether any of these might help my problem?

[gmayor]

The sample was based on the settings I have here which has an assortment of object libraries checked that will be irrelevant to the template and may not even be available to your users. Check VBA tools > references and disable them one at a time and ensure that it still works.
If that doesn't address the problem what are the differing results your users are finding? There is nothing complicated about your template.

[HTSCF Fareha]

The form is just being used by myself at the moment so that I could use it for a bit and check for any possible issues.

I've done as you have suggested and now have just the 'Visual Basic for Applications', 'Microsoft Word 16 Object Library' and 'Microsoft Forms 2' references enabled. There was only the 'OLE Automation' and 'Microsoft Office 16' references that I've removed that still allows the form to run.

I shall report back on my findings after a few more days of usage.

[HTSCF Fareha]

On the whole this seems to be working okay. There is one thing that I have noticed in that when one clicks the "Edit Document" ribbon button, all the text bunches together on the UserForm and doesn't maintain the paragraph spacing. Click "Enter" again and the paragraphs show correctly on the document.

When copying the finished text from the document and pasting into our bespoke system that accepts RTF format, the paragraphs disappear as per that in the UserForm. Really strange!

Whilst looking at the formatting, is there a way to ensure that there is only a single space between words and no blank spaces at the start of any paragraph? You'd be surprised how many pieces of copied / pasted text has extra spaces or blank spaces at start of any paragraph. I think there are some people that type so quickly that they are unable to control word spacing!

Thanks!

[HTSCF Fareha]

I think I've found the answer to the single spacing between words part of my query in #10.



' Ensure there is only single spacing between words
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
'Here is where it is actually looking for spaces between words
.Text = " [ ]@([! ])"
'This line tells it to replace the excessive spaces with one space
.Replacement.Text = " \1"
.MatchWildcards = True
.Wrap = wdFindStop
.Format = False
.Forward = True
.Execute Replace:=wdReplaceAll
End With

[HTSCF Fareha]

I've now managed to (I think) tackle the single word spacing and remove empty paragraphs at the start and end.

Only bit now is when pressing the "Edit Document" ribbon button, all the text bunches together on the UserForm and doesn't maintain the paragraph spacing. Click "Enter" again and the paragraphs show correctly on the document.

When copying the finished text from the document and pasting into our bespoke system that accepts RTF format, the paragraphs disappear as per that in the UserForm like this:-

Chancellor Alexander Schallenberg said it would last a maximum of 20 days and there would be a legal requirement to get vaccinated from 1 February 2022.
He was responding to record case numbers and one of the lowest vaccination levels in Western Europe.
Many other European countries are imposing restrictions as cases rise.
"We don't want a fifth wave," said Mr Schallenberg after meeting the governors of Austria's nine provinces at a resort in the west of the country.
For a long time, there had been a consensus over avoiding mandatory vaccinations, the chancellor said.
However, too many people had been incited not to get the jab, because of "too many political forces, flimsy vaccination opponents and fake news", he added. The measures are yet to be finalised.


Option Explicit

Sub CreateDoc()
Dim oDoc As Document
Dim oRng As Range
Dim oRngPara As Range
Dim oCC As ContentControl
Dim oFrmPPN1 As frmPPN1

If ActiveDocument = ThisDocument Then
MsgBox "You cannot use this function to edit the document template", vbCritical
Exit Sub
End If

Set oDoc = ActiveDocument
Set oFrmPPN1 = New frmPPN1
With oFrmPPN1

For Each oCC In oDoc.ContentControls
If oCC.ShowingPlaceholderText = False Then
Select Case oCC.Title
Case "PPN1"
.txtPPN1.Text = oCC.Range.Text
End Select
End If
Next oCC

.Show
If .Tag = 0 Then GoTo lbl_Exit

For Each oCC In oDoc.ContentControls
'Set a range to the content control
Set oRng = oCC.Range

Select Case oCC.Title
Case "PPN1"
'Fill the range with the content of the text box
oRng.Text = .txtPPN1.Text

Application.ScreenUpdating = False

' Ensure there is only single spacing between words
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
'Here is where it is actually looking for spaces between words
.Text = " [ ]@([! ])"
'This line tells it to replace the excessive spaces with one space
.Replacement.Text = " \1"
.MatchWildcards = True
.Wrap = wdFindStop
.Format = False
.Forward = True
.Execute Replace:=wdReplaceAll
End With

' Ensure there is only single line spacing between paragraphs
With oRng.Find
.MatchWildcards = True
.Text = "[^13]{2,}"
.Replacement.Text = "^p"
.Execute Replace:=wdReplaceAll
.Text = "[^l]{2,}"
.Replacement.Text = "^l"
.Execute Replace:=wdReplaceAll
.Text = "[^13^l]{2,}"
.Replacement.Text = "^p"
.Execute Replace:=wdReplaceAll
End With

'Convert to sentence case
oRng.Case = wdTitleSentence

' Ensure any first or last empty paragraphs are removed
Set oRngPara = ActiveDocument.Paragraphs(1).Range
If oRngPara.Text = vbCr Then oRngPara.Delete

Set oRngPara = ActiveDocument.Paragraphs.Last.Range
If oRngPara.Text = vbCr Then oRngPara.Delete

Application.ScreenUpdating = True

End Select
Next oCC
End With

lbl_Exit:
Unload oFrmPPN1
Set oFrmPPN1 = Nothing
Set oRng = Nothing
Set oRngPara = Nothing
Set oCC = Nothing
Set oDoc = Nothing
Exit Sub
End Sub

[HTSCF Fareha]

Getting really close now. Only issue is if there are extra empty paragraphs at the end, the code fails with a Runtime Error 5941: The requested member of the collection does not exist on this line


If oRng.Paragraphs(x).Range.Text = vbCr And oRng.Paragraphs(x - 1).Range.Text = vbCr Then

Here is the full code that has also solved the "missing" paragraphs issue.


Option Explicit

Sub CreateDoc()
Dim oDoc As Document
Dim oRng As Range
Dim oRngPara As Range
Dim oParagraphCount As Long
Dim x As Integer
Dim oCC As ContentControl
Dim oFrmPPN1 As frmPPN1

If ActiveDocument = ThisDocument Then
MsgBox "You cannot use this function to edit the document template", vbCritical
Exit Sub
End If

Set oDoc = ActiveDocument
Set oFrmPPN1 = New frmPPN1
With oFrmPPN1

For Each oCC In oDoc.ContentControls
If oCC.ShowingPlaceholderText = False Then
Select Case oCC.Title
Case "PPN1"
.txtPPN1.Text = oCC.Range.Text
End Select
End If
Next oCC

.Show
If .Tag = 0 Then GoTo lbl_Exit

For Each oCC In oDoc.ContentControls
'Set a range to the content control
Set oRng = oCC.Range

Select Case oCC.Title
Case "PPN1"
'Fill the range with the content of the text box
oRng.Text = .txtPPN1.Text

Application.ScreenUpdating = False

' Ensure there is only single spacing between words
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
'Here is where it is actually looking for spaces between words
.Text = " [ ]@([! ])"
'This line tells it to replace the excessive spaces with one space
.Replacement.Text = " \1"
.MatchWildcards = True
.Wrap = wdFindStop
.Format = False
.Forward = True
.Execute Replace:=wdReplaceAll
End With

' Ensure there is only single paragraph spacing
oParagraphCount = oDoc.Paragraphs.Count

'Loop Through Each Paragraph (in reverse order)
For x = oParagraphCount To 1 Step -1
If x - 1 > 1 Then
If oRng.Paragraphs(x).Range.Text = vbCr And oRng.Paragraphs(x - 1).Range.Text = vbCr Then
oRng.Paragraphs(x).Range.Delete
End If
End If
Next x

'Convert to sentence case
oRng.Case = wdTitleSentence

' Ensure any first or last empty paragraphs are removed
Set oRngPara = oDoc.Paragraphs(1).Range
If oRngPara.Text = vbCr Then oRngPara.Delete

Set oRngPara = oDoc.Paragraphs.Last.Range
If oRngPara.Text = vbCr Then oRngPara.Delete

Application.ScreenUpdating = True

End Select
Next oCC
End With

lbl_Exit:
Unload oFrmPPN1
Set oFrmPPN1 = Nothing
Set oRng = Nothing
Set oRngPara = Nothing
Set oCC = Nothing
Set oDoc = Nothing
Exit Sub
End Sub

[gmayor]

Maybe

Case "PPN1"
oCC.LockContentControl = False
oCC.LockContents = False
oRng.Text = .txtPPN1.Text
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
'Here is where it is actually looking for spaces between words
.Text = "[ ]{1,}"
'This line tells it to replace the excessive spaces with one space
.Replacement.Text = " "
.MatchWildcards = True
.Wrap = wdFindStop
.Format = False
.Forward = True
.Execute Replace:=wdReplaceAll
End With
'Loop Through Each Paragraph (in reverse order)
For x = oRng.Paragraphs.Count To 1 Step -1
If Len(oRng.Paragraphs(x).Range) = 1 Then oRng.Paragraphs(x).Range.Delete
Next x
oRng.Paragraphs.Last.Range.Characters.Last.Delete
'Convert to sentence case
oRng.Case = wdTitleSentence

[HTSCF Fareha]

Sorry, Graham, this does not remove the preceding or end empty paragraphs. The same error pops up. :(

It also loses the paragraph spacing where there is text.

[gmayor]

It works for me - see attached.
I am appalled to see that your quote has materialised in Austria. It is interesting to note that the last time such an action was taken, it was by the last memorable Austrian, and look where that led. Here in Cyprus our president plans the same outrageous approach. This is not going to address the problem or end well. May I suggest that you listen to last night's comment by Neil Oliver on GB News.

[HTSCF Fareha]

Graham, I think I need to explain that the piece of text that I used was as a quick copy of a main news item on the BBC website and was used purely to demonstrate my problem and nothing else. Thinking about my choice of text now I had probably inadvertedly broken a forum rule by posting something political. I humbly and respectfully apologise if this is the case.

Alas, I am still not achieving the right results. I have managed to sort out any blank paragraphs at the start of anything pasted, but any end paragraphs I cannot deal with and have had to comment out the two lines of code.

To perform the removal of empty paragraphs at the beginning I've used a loop and this appears to function well. I've attached my latest offering.

Here are two images which show the ideal finished product (again I have used a BBC news article, although have avoided politics).

2917429175

[gmayor]

Your latest effort adds empty paragraphs to the document which is bad practice. Use paragraph spacing. If you want to display spacing in the text box when using the edit button, you can do that by adding the empty paragraphs to the text box as you read back the content of the content control. See attached.

[HTSCF Fareha]

Many thanks Graham, that's cracked it! :thumb

I'd love to know what the following two lines of code do with regard to the Content Controls.


oCC.LockContentControl = False
oCC.LockContents = False

Also trying to fathom how this line works.


oRng.Paragraphs.Last.Range.Characters.Last.Delete

Am I right in saying that it looks at the last paragraph and then for any characters it might contain before deleting anything found?

[gmayor]

Some edits are not possible if content controls or their contents are locked. The first two lines simply ensure they are not locked before attempting the edit. You can lock them after processing if you don't want users to meddle.
https://www.gmayor.com/insert_content_control_addin.htm (http://www.gmayor.com/insert_content_control_addin.htm) is a simple means of inserting content controls and editing them. I usually have it set to lock the controls when adding them, so they don't get 'accidentally' deleted,

The other piece of code simply removes the last character in the content control range. If that last character is a paragraph break ¶ then it removes that break, however you cannot remove the character unless it is in an empty last paragraph and so it is a means to remove an empty last paragraph.

[HTSCF Fareha]

Thanks for the explanation. I already have your CC addin, which is very useful!