Removing vbCrLf text copy/pasted from a userform textbox going to a Word Formfield

[keaden]

Hello folks,
I'm having a bit of bother with a final part of a userform I'm working on.

The userform copys text from multiline textboxes to dynamically created form fields in a word document, the problem I'm having is that the pasted text has unwanted spaces at the start and end of each line within the copied form field.

I have been trying to remove the unwanted characters but haven't had much luck (I've been going on the assumption that it's a problem with vbCrLf being copied over from the multiline textboxes, any help on fixing this or pointing me in the right direction would be appreciated.

Here's the code;

Private iAddedCount As Integer

Private Sub CommandButton10_Click()
    iAddedCount = iAddedCount + 1
    Dim theCombo As Control
    Dim theTextBox800 As Control
    Dim theTextBox801 As Control
    Dim objLabel1 As Control
    Dim objLabel2 As Control
    Dim objLabel3 As Control
    
        
        Set theCombo = OSRData.MultiPage1.Pages(2).Controls.Add("Forms.Combobox.1", True)
        Set theTextBox800 = OSRData.MultiPage1.Pages(2).Controls.Add("Forms.Textbox.1", True)
        Set theTextBox801 = OSRData.MultiPage1.Pages(2).Controls.Add("Forms.Textbox.1", True)
        Set objLabel1 = OSRData.MultiPage1.Pages(2).Controls.Add("Forms.Label.1", True)
        Set objLabel2 = OSRData.MultiPage1.Pages(2).Controls.Add("Forms.Label.1", True)
        Set objLabel3 = OSRData.MultiPage1.Pages(2).Controls.Add("Forms.Label.1", True)
            
            With theCombo
                    .Name = "Combo" & iAddedCount
                    .Height = 15
                    .Left = 10
                    .Width = 300
                    .Top = 25 * iAddedCount
                    .RowSource = "Article"
                    .ListIndex = 0
            End With
            
            With theTextBox800
                    .Name = "Text1" & iAddedCount
                    .Height = 15
                    .Left = 330
                    .Width = 180
                    .Top = theCombo.Top
                    .MultiLine = True
                    .EnterKeyBehavior = True
                    .ScrollBars = fmScrollBarsVertical
            End With
            
            With theTextBox801
                    .Name = "Text2" & iAddedCount
                    .Height = 15
                    .Left = 520
                    .Width = 180
                    .Top = theCombo.Top
                    .MultiLine = True
                    .EnterKeyBehavior = True
                    .ScrollBars = fmScrollBarsVertical
            End With
            
            With objLabel1
                    .Name = "Label100" & iAddedCount
                    .Font.Size = 8
                    .BackColor = Me.BackColor
                    .Caption = "Article"
                    .Height = 10
                    .Left = 10
                    .Width = 60
                    .Top = theCombo.Top - 10
            End With
            
            With objLabel2
                    .Name = "Label200" & iAddedCount
                    .Font.Size = 8
                    .BackColor = Me.BackColor
                    .Caption = "Observation(s)"
                    .Height = 10
                    .Left = 330
                    .Width = 60
                    .Top = theCombo.Top - 10
            End With
            
            With objLabel3
                    .Name = "Label300" & iAddedCount
                    .Font.Size = 8
                    .BackColor = Me.BackColor
                    .Caption = "Action(s)"
                    .Height = 10
                    .Left = 520
                    .Width = 50
                    .Top = theCombo.Top - 10
            End With


End Sub

Private Sub CommandButton4_Click()    Dim objWord As Word.Application
    Dim objDoc As Word.Document
    Dim objExcel As Object
    Dim i As Integer
    Dim theCombo As Control
    Dim theTextBox800 As Control
    Dim theTextBox801 As Control
    Dim x As Integer
    Dim y As Integer
    
    x = iAddedCount
    y = 1
    
    Set objWord = CreateObject("Word.Application")
    objWord.DisplayAlerts = False
    objWord.Visible = True
    objWord.Documents.Open ActiveWorkbook.Path & "\Doc.dotm", ReadOnly:=True '"\test.dotx", ReadOnly:=True
    objWord.Activate


        For Each theCombo In OSRData.MultiPage1.Pages(2).Controls
                
                If x > 0 Then
                    objWord.Selection.FormFields.Add Range:=objWord.Selection.Range, Type:= _
                    wdFieldFormTextInput
                        With objWord.Dialogs(wdDialogFormFieldOptions)
                            .Name = ("Com" & y)
                            .Execute
                            objWord.Selection.MoveRight
                            objWord.Selection.TypeParagraph
                        End With
                                      
                                      
                    objWord.Selection.FormFields.Add Range:=objWord.Selection.Range, Type:= _
                    wdFieldFormTextInput
                        With objWord.Dialogs(wdDialogFormFieldOptions)
                            .Name = ("Text1" & y)
                            .Execute
                            objWord.Selection.MoveRight
                            objWord.Selection.TypeParagraph
                        End With
                     
                        
                    objWord.Selection.FormFields.Add Range:=objWord.Selection.Range, Type:= _
                    wdFieldFormTextInput
                        With objWord.Dialogs(wdDialogFormFieldOptions)
                            .Name = ("Text2" & y)
                            .Execute
                            objWord.Selection.MoveRight
                            objWord.Selection.TypeParagraph
                        End With
                        
                        
                        With objWord.ActiveDocument
                            .FormFields("Com" & y).Result = Me("Combo" & y).Text
                            .FormFields("Text1" & y).Result = Me("Text1" & y).Text
                            .FormFields("Text2" & y).Result = Me("Text2" & y).Text


                            
                        End With


                End If
                    y = y + 1
                    x = x - 1
       Next theCombo


End Sub

Thanks for reading
K

 

[mrmmickle1]

Try looking at this thread it helps to remove "invisible" characters:

http://www.mrexcel.com/forum/excel-questions/3779-macro-delete-hidden-characters-text.html

 

[keaden]

Try looking at this thread it helps to remove "invisible" characters:

http://www.mrexcel.com/forum/excel-questions/3779-macro-delete-hidden-characters-text.html

Thanks for the point in the right direction mrmmickle,
I managed to get it sorted by using another string variable to replace the vbLf and use that new variable instead. The code is below.

    Dim Txt1 As String
    Dim Txt2 As String
    Dim Txt1Rplce As String
    Dim Txt2Rplce As String

                            Txt1 = Me("Text1" & y).Text
                            
                            Txt2 = Me("Text2" & y).Text
                            
                            Txt1Rplce = Replace(Txt1, vbLf, "")
                            
                            Txt2Rplce = Replace(Txt2, vbLf, "")
                            
                            .FormFields("Text1" & y).Result = "Observations:" & vbCr & Txt1Rplce
                            
                            .FormFields("Text2" & y).Result = "Action(s):" & vbCr & Txt2Rplce

Thanks again
K