Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 21

Thread: Entered Text On UserForm Not Filling CC On Document

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    364
    Location

    Entered Text On UserForm Not Filling CC On Document

    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.

    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.
    Attached Files Attached Files

  2. #2
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    364
    Location
    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)

  3. #3
    Fixed! See attached.
    Attached Files Attached Files
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  4. #4
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    364
    Location
    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!!

    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

  5. #5
    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))
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  6. #6
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    364
    Location
    Absolutely spot on, thank you.

  7. #7
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    364
    Location
    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....ase-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?
    Last edited by HTSCF Fareha; 10-30-2021 at 11:18 PM.

  8. #8
    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.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  9. #9
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    364
    Location
    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.

  10. #10
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    364
    Location
    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!
    Attached Files Attached Files

  11. #11
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    364
    Location
    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

  12. #12
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    364
    Location
    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

  13. #13
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    364
    Location
    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

  14. #14
    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  15. #15
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    364
    Location
    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.

  16. #16
    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.
    Attached Files Attached Files
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  17. #17
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    364
    Location
    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).

    Word-1.jpgWord-2.jpg
    Attached Files Attached Files

  18. #18
    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.
    Attached Files Attached Files
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  19. #19
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    364
    Location
    Many thanks Graham, that's cracked it!

    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?

  20. #20
    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 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.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •