• Userforms and custom document properties in Word VBA

    Home » Forums » AskWoody support » Productivity software by function » Visual Basic for Applications » Userforms and custom document properties in Word VBA

    Author
    Topic
    #494958

    Hi all,

    First post here – I’ve been lurking for a few weeks trying to work on a small project and the help here has been incredible, but I’m still having some problems!

    I’ve got a userform that fills in the blanks in a document. Having used some of the excellent code examples on here and elsewhere, I originally tried to use bookmarks to insert the values – which does the job well, were it not for the fact that I need to insert the info from the bookmarks in more than one place. I can’t get the bookmark crossreferencing trick to work, so I decided to go down (as I’ve seen recommended elsewhere) the custom document property route.

    I’ve hit a bit of a brick wall with the code though – I can’t see where I’m going wrong, but I keep getting an argument not optional error.

    Code:
    Option Explicit
    
    Private Sub cboSenderAddress_Change()
    
    End Sub
    
    Private Sub cmdCancel_Click()
        Unload Me
        ActiveDocument.Close SaveChanges:=False
    End Sub
    
    Private Sub cmdClear_Click()
        
        txtInsured.Value = Null
        txtCWDescription.Value = Null
        txtSalutation.Value = Null
        cboPII.Value = Null
        cboInsuredRole.Value = Null
        cboParty2Role.Value = Null
        txtParty2.Value = Null
        txtParty3.Value = Null
        cboParty3Role.Value = Null
        cboStepIn.Value = Null
    End Sub
    
    Private Sub cmdOK_Click()
    WriteCustomProp(“Insured”).Range.Text , txtInsured.Value, msoPropertyTypeString
    WriteCustomProp(“CWDescription”).Range.Text , txtCWDescription.Value, msoPropertyTypeString
    WriteCustomProp(“PII”).Range.Text , cboPII.Value, msoPropertyTypeString
    WriteCustomProp(“InsuredRole”).Range.Text , cboInsuredRole.Value, msoPropertyTypeString
    WriteCustomProp(“Party2Role”).Range.Text , cboParty2Role.Value, msoPropertyTypeString
    WriteCustomProp(“Party2”).Range.Text , txtParty2.Value, msoPropertyTypeString
    WriteCustomProp(“Party3”).Range.Text , txtParty3.Value, msoPropertyTypeString
    WriteCustomProp(“Party3Role”).Range.Text , cboParty3Role.Value, msoPropertyTypeString
        ActiveDocument.Fields.Update
        End With
        Application.ScreenUpdating = True
        Unload Me
    End Sub
    Function WriteCustomProp(sProp As String, sValue As String, iType As Integer) As Boolean
      Dim prop As DocumentProperty, bExists As Boolean
      bExists = False
      For Each prop In ActiveDocument.CustomDocumentProperties
        If LCase(prop.Name) = LCase(sProp) Then
          bExists = True
          prop.Value = sValue
          Exit For
        End If
      Next
      If Not bExists Then
        ActiveDocument.CustomDocumentProperties.Add Name:=sProp, Value:=sValue, _
              LinkToContent:=False, Type:=iType
      End If
    End Function
    
    Private Sub Label1_Click()
    
    End Sub
    
    Private Sub Label10_Click()
    
    End Sub
    
    Private Sub Label14_Click()
    
    End Sub
    
    Private Sub optGreeting1_Click()
    
    End Sub
    
    Private Sub txtInsured_Change()
    
    End Sub
    
    Private Sub UserForm_Initialize()
        
        With cboInsuredRole
            .AddItem “Contractor”
            .AddItem “Subcontractor”
            .AddItem “Trade Contractor”
        End With
        With cboParty2Role
            .AddItem “Contractor”
            .AddItem “Funder”
            .AddItem “Employer”
            .AddItem “Local Authority”
        End With
        With cboParty3Role
            .AddItem “Contractor”
            .AddItem “Funder”
            .AddItem “Employer”
            .AddItem “Local Authority”
        End With
    End Sub
    

    If anyone can help me I would be very, very grateful – if you can explain it to me as if I’m a five year old, that would be even more helpful!

    Credit to Andrew Lockton for snippets of the code above, great to learn from.

    Thanks!

    PS it’s still a WIP so some of the references to labels etc haven’t been filled in yet.

    Viewing 5 reply threads
    Author
    Replies
    • #1454788

      The major problem I can see is how you are calling the WriteCustomProp function. It is expecting three inputs – String, String, Integer. You don’t need to use brackets since this function is not being used to return a value and should probably also be corrected to remove the ‘As Boolean’ from its name.

      Note that this code IN ITSELF doesn’t place the values anywhere in the text – hence the .Range.Text is meaningless. The code writes the values to document properties and you will need to ensure that anywhere you want this information to appear in the document you have fields there that point to it.

      Try the following.

      Code:
      Private Sub cmdOK_Click()
        WriteCustomProp "Insured", txtInsured.Value, msoPropertyTypeString
        WriteCustomProp "CWDescription", txtCWDescription.Value, msoPropertyTypeString
        WriteCustomProp "PII", cboPII.Value, msoPropertyTypeString
        WriteCustomProp "InsuredRole", cboInsuredRole.Value, msoPropertyTypeString
        WriteCustomProp "Party2Role", cboParty2Role.Value, msoPropertyTypeString
        WriteCustomProp "Party2", txtParty2.Value, msoPropertyTypeString
        WriteCustomProp "Party3", txtParty3.Value, msoPropertyTypeString
        WriteCustomProp "Party3Role", cboParty3Role.Value, msoPropertyTypeString
        
        ActiveDocument.Fields.Update
        Application.ScreenUpdating = True
        Unload Me
      End Sub
    • #1454885

      Hi nocarsgo,

      I’m surprised you couldn’t get the Bookmark/Ref Field thing working. If you elaborate, perhaps someone can help with that, too.

      Are you preparing your template/VBA project for others to use? Is there a reason you’re using a method of populating with fields? Do these bits of info need to be edited once they’ve been added to the document? Can you simply insert the data multiple times as simple text? Then if adjustments are needed, they can be done with search and replace. Sometimes a simpler approach is all you need.

      I say this because if you’re doing this for others, you have to account for the stumbling blocks your users may encounter. Bookmarks are easy to delete, which causes an error in the Ref fields, but you can at least display bookmarks for your users and caution them not to edit carelessly around them. With document properties, confusion often comes when someone wants to make a change to the info saved in a property: if a user does a search and replace to fix a typo, they will find the change undone on printing or saving… this can be mystifying. Editing a document property is easy enough, but now you’re straying into the realm of having your automation potentially more difficult than just inserting data manually.

      What are your user parameters? Are they savvy Word users or a mix? I know I’ve had a fancy automation solution blow up in my face just because it was too fancy for the average user — and they’re the whole reason I automate templates in the first place. Just sayin’…

      Best, Kim

    • #1454898

      Kim – I’m going down the userform route as a learning experience and because we use userforms quite regularly in work, so my colleagues are quite familiar with forms in Word. In addition, it’s not just a case of find and replace – I’ll be including certain clauses and deleting others depending on which box is ticked, etc. But thank you for your advice re: making sure I don’t overcomplicate things. 🙂

      To expand on the bookmarks point – I’ve included my code below. Essentially, the code works fine and the value writes to the bookmark – but even if I hit Ctrl + F9 and type (brackets) REF InsuredRole (/brackets), and include a line in the form to update fields, the fields aren’t updated. Unless I’m doing something remarkably silly with my code – I’ve included it below!

      Code:
      Option Explicit
      
      Private Sub cboSenderAddress_Change()
      
      End Sub
      
      Private Sub cmdCancel_Click()
          Unload Me
          ActiveDocument.Close SaveChanges:=False
      End Sub
      
      Private Sub cmdClear_Click()
          txtInsured.Value = Null
          txtCWDescription.Value = Null
          cboPII.Value = Null
          cboInsuredRole.Value = Null
          cboParty2Role.Value = Null
          txtParty2.Value = Null
          txtParty3.Value = Null
          cboParty3Role.Value = Null
          cboStepIn.Value = Null
      End Sub
      
      Private Sub cmdOK_Click()
          Application.ScreenUpdating = False
          With ActiveDocument
              .Bookmarks(“Insured”).Range.Text = txtInsured.Value
              .Bookmarks(“CWDescription”).Range.Text = txtCWDescription.Value
              .Bookmarks(“PII”).Range.Text = cboPII.Value
              .Bookmarks(“InsuredRole”).Range.Text = cboInsuredRole.Value
              .Bookmarks(“Party2Role”).Range.Text = cboParty2Role.Value
              .Bookmarks(“Party2”).Range.Text = txtParty2.Value
              .Bookmarks(“Party3Role”).Range.Text = cboParty3Role.Value
              .Bookmarks(“Party3”).Range.Text = txtParty3.Value
              .Fields.Update
          End With
          Application.ScreenUpdating = True
          Unload Me
      End Sub
      
      Private Sub Label1_Click()
      
      End Sub
      
      Private Sub Label10_Click()
      
      End Sub
      
      Private Sub Label14_Click()
      
      End Sub
      
      Private Sub UserForm_Initialize()
          optGreeting1.Value = True
          With cboInsuredRole
              .AddItem “Contractor”
              .AddItem “Subcontractor”
              .AddItem “Trade Contractor”
          End With
          With cboParty2Role
              .AddItem “Contractor”
              .AddItem “Funder”
              .AddItem “Employer”
              .AddItem “Local Authority”
          End With
          With cboParty3Role
              .AddItem “Contractor”
              .AddItem “Funder”
              .AddItem “Employer”
              .AddItem “Local Authority”
          End With
      End Sub
      

      Andrew – thank you for your help, it is very much appreciated. So, if I understand you correctly, I can amend the function as:

      Code:
      Function WriteCustomProp(sProp As String, sValue As String)
        Dim prop As DocumentProperty, bExists
        bExists = False
        For Each prop In ActiveDocument.CustomDocumentProperties
          If LCase(prop.Name) = LCase(sProp) Then
            bExists = True
            prop.Value = sValue
            Exit For
          End If
        Next
        If Not bExists Then
          ActiveDocument.CustomDocumentProperties.Add Name:=sProp, Value:=sValue, _
                LinkToContent:=False, Type:=iType
        End If
      End Function

      If I do *that*, however, I get a “wrong number of arguments” error when I call the function. Have I misunderstood what you were referring to?

      And I should have mentioned in my previous post – I’ve slotted into the document a number of fields that refer to the docprops.

    • #1454920

      The WriteCustomProp should be amended to

      Code:
      Function WriteCustomProp(sProp As String, sValue As String, iType As Integer)
        Dim prop As DocumentProperty, bExists As Boolean
        bExists = False
        For Each prop In ActiveDocument.CustomDocumentProperties
          If LCase(prop.Name) = LCase(sProp) Then
            bExists = True
            prop.Value = sValue
            Exit For
          End If
        Next
        If Not bExists Then
          ActiveDocument.CustomDocumentProperties.Add Name:=sProp, Value:=sValue, _
                LinkToContent:=False, Type:=iType
        End If
      End Function

      I’m not sure whether you are exploring two methods (as a learning experience) or whether you are just confused. The Document Properties method uses DocProperty fields; the bookmarks method uses Ref fields.

      If you are exploring the Bookmarks method, you will discover that your code is actually deleting the bookmark (and the text inside it) and replacing it with the userform control value. See http://www.word.mvps.org/FAQs/MacrosVBA/InsertingTextAtBookmark.htm for a way to solve this problem.

    • #1454926

      Hi nocarsgo,

      About adjusting your code for the bookmarks — what Andrew said. 🙂

      Best, Kim

      • #1459716

        this might sound a little awkward after all the expert replies on the title line.

        i work mainly on word documents by creating a right.click>new word document in a folder, then double clicking to edit straightaway.
        i have this uncanny habit of giving every word file a “Document number” from within word. alt+f+i then custom, scroll down the name to “Document number”, Type=Number and giving a unique number to Value.

        having to enter this manually in every new file beats the holy disciple outta me.

        it’d be a lot good easy on me if i started editing a file, and word asks me to enter my ‘unique’ number, then word automatically assigns the value as mentioned above.

        for a newbie, i tried
        For Each pt In ActiveDocument.BuiltInDocumentProperties
        MsgBox pt
        Next
        but i keep getting this when i run the macro

        run-time error ‘-2147467259 (80004005)’
        Automation error
        Unspecified error

        then the debug click rests the hilite on MsgBox pt line.

        i’m in dire need of a help out here.

        zambi

    • #1459738

      Zambi,

      I don’t know why the error is being generated but you can get around it like this:

      Code:
      Option Explicit
      
      Sub Test()
        Dim pt As Object
        
        GoTo MainTest
      PropError:
         Debug.Print pt.Name
         Resume Next
      MainTest:
        On Error GoTo PropError
         For Each pt In ActiveDocument.BuiltInDocumentProperties
            MsgBox pt.Name & ": " & pt.Value
         Next
         
         On Error GoTo 0
         
      End Sub
      

      37424-DocProps

      Note: pt is an Object and when your reference it w/o a qualifier you get the Default Property. Notice the list of properties that will generate an error in the Immediate window above.

      HTH :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

    Viewing 5 reply threads
    Reply To: Userforms and custom document properties in Word VBA

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: