Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Star Lounger
    Join Date
    Oct 2011
    Location
    Auckland. New Zealand
    Posts
    55
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Populating many occurrences of field in document from UserForm

    Up until now I've created templates with a UserForm where the user inputs text (e.g. ClientName). I set up bookmarks in the template so when the user enters the fields in the UserForm and clicks on OK, the appropriate bookmarks in the document are populated with the entered data.

    This works fine but I am creating a template for a 16 page RFP document and it has many occurances of each of the three fields that the user will be entering in the UserForm. (e.g. at least 30 places where I want to insert the ClientName.) Apart from setting up lots of bookmarks in the template, is there another way of populating a large number of fields?

  2. 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,514
    Thanks
    3
    Thanked 143 Times in 136 Posts
    I use Content Controls for this type of thing now. They update instantly and your form just needs to update the built-in document property to make it all happen.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #3
    Star Lounger
    Join Date
    Oct 2011
    Location
    Auckland. New Zealand
    Posts
    55
    Thanks
    8
    Thanked 0 Times in 0 Posts
    I have used Content Controls to insert a Date Picker before. Do you use them in conjunction with the UserForm? Can you give me a bit more info about how you use them? I've spent ages googling them in the past and haven't found a really good explanation and examples of use.

  5. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,894
    Thanks
    0
    Thanked 188 Times in 172 Posts
    Since you're using a userform, simply have it populate a single custom document property for each of the inputs, then use DOCPROPERTY fields in the document to replicate the data wherever required. No bookmarks are needed for this and, after populating the custom document properties, only a single command to update all the fields. For example:
    Code:
    Private Sub CommandButton1_Click()
    With ActiveDocument
      .CustomDocumentProperties("Client") = Me.TextBox1.Text
      .CustomDocumentProperties("Address") = Me.TextBox2.Text
      .Fields.Update
    End With
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #5
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,514
    Thanks
    3
    Thanked 143 Times in 136 Posts
    Niki

    I have attached a demo file. The easiest way to do this is to use the built-in Content Controls by going to Insert > Quick Parts > Document Property. Put the same entry in each location. Then link your user form to update that particular property eg.
    Code:
    Private Sub UserForm_Initialize()
      Me.tbTitle = ActiveDocument.BuiltInDocumentProperties("Title")
    End Sub
    
    Private Sub cmbOK_Click()
      ActiveDocument.BuiltInDocumentProperties("Title") = Me.tbTitle
      Me.Hide
    End Sub
    If you need to go beyond the subset of document properties available in the quick parts then it gets more complicated but if you only need a few fields then you should just use the built-in properties that are available as content controls.
    Attached Files Attached Files
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  7. #6
    Star Lounger
    Join Date
    Oct 2011
    Location
    Auckland. New Zealand
    Posts
    55
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Wahoo. Thank you Andrew - it works a treat. Can you tell me what the me. means? (I got it to work but I don't know quite why.) Are you also able to explain the difference between your solution and Paul's one? (BuiltInDocumentProperties vs CustomDocumentProperties?)

  8. #7
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,514
    Thanks
    3
    Thanked 143 Times in 136 Posts
    The Me. refers to the parent object (in this case the userform which contains the code). It is not really necessary for the code to run since this is the default context for the textboxes being referred to but I like to put it in anyway.

    The BuiltInDocumentProperties vs the CustomDocumentProperties is largely irrelevant. Some document properties are contained in the Built-In document properties (such as Title and Subject) whilst the less commonly used ones are in the CustomDocumentProperties (like Client and Address). The actual name of the document property will guide which one you use.

    The bigger difference between Paul's and my solutions are the use of fields vs the use of content controls. I prefer to use the content controls because they update instantly and can be edited in the document to reflect back into the document properties. The fields require the update command to show changes and can't be edited in the document. The drawback of using content controls is they are converted to fields if the document is saved in doc format.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  9. #8
    Star Lounger
    Join Date
    Oct 2011
    Location
    Auckland. New Zealand
    Posts
    55
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks for your help - it's becoming clearer (but the waters are still quite muddy).

    Having got that going I thought I'd be a bit more clever with one of my other templates (a little knowledge is a dangerous thing). This template uses values that the user enters to populate the file name (sound familiar??? I've posted about this template/functionality before).

    Two of the fields the user must enter are Subject and Job No so I set up Document Properties for these. Out of the possible options (I counted 15) when you click on Insert, Quick Parts, Document Property, I selected two - Subject (for the subject!), and Company Fax (for the Job No).

    I used the following statement to find out what subject the user had entered:
    SaveSubject = ActiveDocument.BuiltInDocumentProperties("Subject" ) and it worked fine.

    This didn't work for the Company Fax so I thought "aha", it must be a CustomDocumentProperty. However, when I tried to use the following it didn't work:
    SaveJobNo = ActiveDocument.CustomDocumentProperties("Company Fax") I get a Run-time Error 5 - Invalid Procedure Call or Argument. How do I access this Document Property?

    (Before you start thinking that all I have to do is use the Document Property "Title" and it'll work, I have something like 7 - 9 of these values that I want to assign to Document Properties so I suspect I'm going to have to end up using a CustomDocumentProperty for some of them.)

    While I'm hoping that setting up Document Properties is more robust than bookmarks, I am finding a couple of disadvantages:
    1. I can still delete them easily. (But at least it's easy to see that I've deleted it)
    2. I have to use the mouse to highlight the prompt I'm entering before I type in the value. (When I create bookmarks and use "Macrobutton nomacro", you can click anywhere in the line to overtype the value.)
    Any comments or suggestions on these 'challenges' would be helpful.
    Last edited by Niki B; 2012-10-16 at 23:19. Reason: Inserted more detail.

  10. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,894
    Thanks
    0
    Thanked 188 Times in 172 Posts
    Are you sure you have a 'Company Fax' property? The following macro will list all the existing properties and their values on a new page at the end of the document:
    Code:
    Sub ListProps()
    Dim Prop As DocumentProperty
    With ActiveDocument
      On Error Resume Next
      .Range.InsertAfter Chr(12) & "Built-In Document Properties"
      For Each Prop In .BuiltInDocumentProperties
        .Range.InsertAfter vbCr & Prop.Name & ": "
        .Range.InsertAfter Prop.Value
      Next
      .Range.InsertAfter vbCr & "Custom Document Properties"
      For Each Prop In .CustomDocumentProperties
        .Range.InsertAfter vbCr & Prop.Name & ": "
        .Range.InsertAfter Prop.Value
      Next
    End With
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  11. The Following User Says Thank You to macropod For This Useful Post:

    kmurdock (2014-03-29)

  12. #10
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,514
    Thanks
    3
    Thanked 143 Times in 136 Posts
    Niki

    You have discovered that the Company Fax item is neither Built-In nor Custom Document Property. Unfortunately, this field appears in the xml files in the namespace of "CoverPageProperties". And before you ask CoverPageProperties("Company Fax") doesn't work

    So your options are considerably shorter than the available list in quick parts if you need to edit the properties directly via VBA.

    There are several workarounds of varying difficulty. The one I think you should use is independent of whatever property you are using.
    Code:
    Dim aCC As ContentControl  For Each aCC In ActiveDocument.ContentControls
        If aCC.Title = "Company Fax" Then
          aCC.Range.Text = "867-5309"  'Jenny's number
        End If
      Next aCC
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  13. #11
    New Lounger
    Join Date
    Mar 2014
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lightbulb I need some do detail

    Quote Originally Posted by macropod View Post
    Since you're using a userform, simply have it populate a single custom document property for each of the inputs, then use DOCPROPERTY fields in the document to replicate the data wherever required. No bookmarks are needed for this and, after populating the custom document properties, only a single command to update all the fields. For example:
    Code:
    Private Sub CommandButton1_Click()
    With ActiveDocument
      .CustomDocumentProperties("Client") = Me.TextBox1.Text
      .CustomDocumentProperties("Address") = Me.TextBox2.Text
      .Fields.Update
    End With
    End Sub
    Could you give me some details or walk me thought on your explanation. My particular problem is that I have a document with repeating information throughout the document. It is tedious to be typing the same information over and over again. I made a userform that contains ComboBoxes and date picker; but I am having problems thus far. My combo boxes was working in the user form but just stop, don't know what I did to cause it to stop working. I used with statement to input combo information and bookmarks to insert the text into the document. Never fully work right. Please Help.

  14. #12
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,514
    Thanks
    3
    Thanked 143 Times in 136 Posts
    Khufu

    You will need to give us better information if you want to know what you have done wrong. You shouldn't need a userform or macros if you simply choose to use the built-in Content Controls which are already linked to built-in document properties.

    As an example to get you started:
    1. Make sure your document is saved in docx or docm format (not .doc)
    2. Go to Insert > Quick Parts > Document Property and choose one of those entries
    3. Repeat step 2 in a few places throughout your document
    4. Now edit the text in one of those instances and click out of the Content Control - you will see all of the locations now show the text you entered.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  15. #13
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,894
    Thanks
    0
    Thanked 188 Times in 172 Posts
    Quote Originally Posted by khufu View Post
    Could you give me some details or walk me thought on your explanation. My particular problem is that I have a document with repeating information throughout the document. It is tedious to be typing the same information over and over again.
    For the post you quoted, create two custom document properties, one named 'Client' and one named 'Address'. Then, wherever you want the 'Client' data to appear in the document, insert a DOCPROPERTY field, referencing 'Client'. Likewise for the 'Address'.
    I made a userform that contains ComboBoxes and date picker; but I am having problems thus far. My combo boxes was working in the user form but just stop, don't know what I did to cause it to stop working.
    Without seeing your code and, preferably, the document you're using it with, it's impossible to say what the issue is. Can you attach a document to a post with some representative data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab.
    I used with statement to input combo information and bookmarks to insert the text into the document. Never fully work right.
    The code in the post to which you replied doesn't use bookmarks; it uses two Custom Document Properties and however many DOCPROPERTY fields you want for each of those.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  16. #14
    New Lounger
    Join Date
    Mar 2014
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    File with user form embeded

    orgin doc.docm

    The file contains a user from with the details. It is self explanatory. I am trying to use the userform to insert information in the document. The document has several fields of similar information to be inserted.

    I am hoping that your guys are able to help me with it.

    Thank you all,

    Khufu

  17. #15
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,894
    Thanks
    0
    Thanked 188 Times in 172 Posts
    I don't see any indication in your document that you've added any custom document properties to it, or that you've inserted any DOCPROPERTY fields to reference them. It's all very well having code to update the custom document properties and fields, but that will only cause an error if the properties don't exist and, similarly, nothing in the document will be updated unless you add the DOCPROPERTY fields to it. The custom document properties can be created via File>Info>properties>Advanced>Custom. Once you've done that, the DOCPROPERTY fields are created wherever you need them via Insert|QuickParts>Field.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Page 1 of 2 12 LastLast

Posting Permissions

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