Userforms and custom document properties in Word VBA

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Hi nocarsgo,

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

Best, Kim

Reply | Quote

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

Reply | Quote

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

Reply | Quote