Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    May 2014
    Posts
    2
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Userforms and custom document properties in Word VBA

    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.
    Last edited by nocarsgo; 2014-05-28 at 17:40. Reason: clarification

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,506
    Thanks
    3
    Thanked 141 Times in 134 Posts
    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
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    nocarsgo (2014-05-29)

  5. #3
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    661
    Thanks
    3
    Thanked 19 Times in 18 Posts
    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

  6. The Following User Says Thank You to kmurdock For This Useful Post:

    nocarsgo (2014-05-29)

  7. #4
    New Lounger
    Join Date
    May 2014
    Posts
    2
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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.

  8. #5
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,506
    Thanks
    3
    Thanked 141 Times in 134 Posts
    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/Macros...AtBookmark.htm for a way to solve this problem.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  9. #6
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    661
    Thanks
    3
    Thanked 19 Times in 18 Posts
    Hi nocarsgo,

    About adjusting your code for the bookmarks -- what Andrew said.

    Best, Kim

  10. #7
    New Lounger
    Join Date
    Jul 2014
    Location
    Delhi,IN
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy "DOCPROPERTY""Document number"""

    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

  11. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 760 Times in 695 Posts
    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
    DocProps.JPG

    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
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


Posting Permissions

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