Search code examples
vbams-wordword-addinsword-template

Deploying Word Macro-Enabled Template (.dotm) with a Custom UI Ribbon

I have built a Word Macro-Enabled Template (.dotm) that works perfectly on my machine when I save it to my Word Startup folder. However, when I send the .dotm file to my colleagues (some on same environment as me and some not) an error is generated when pressing the custom ui ribbon button:

The macro cannot be found or has been disabled because of your security settings

The macro settings are set to run all macros, the folder containing the template >is a trusted location

.... more help options follow

My colleagues also save the file to their startup folder and load Word. The ribbon shows perfectly, but the macro does not run. My colleagues and I have done a boatload of searching online and cannot figure out the issue, as all my configurations are correct, as far as I can tell, which I will list below:

  • Macro Settings: Enable All Macros
  • Trust access to the VBA project object model is checked
  • Startup folder is established as a trusted location

Custom UI XML

<?xml version="1.0" encoding="utf-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
  <ribbon>
    <tabs>
      <tab id="customTab" label="WebMerge">
        <group id="CustomHelp" label="Map Tags">
          <button id="mapper"
                  visible="true"
                  size="large"
                  label="Open Mapper"
                  screentip="Opens Mapping Engine"
                  onAction="LoadMappingGuide"
            imageMso="FindDialog"/>
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

UI Ribbon Connection in Standard Module

Option Explicit

Sub LoadMappingGuide(ByVal Control As IRibbonControl) 'also tried without ByVal

    'select business unit or automatically detect based on username against our database ...
    Dim mappingForm As New mappingGuide
    mappingForm.Show vbModal

End Sub

mappingForm (UserForm) Code

Option Explicit

Private Sub cancelButton_Click()

    Unload Me

End Sub

Private Sub searchBox_Change()

    generateList

    Dim n As Long, index As Long

    index = 0

    For n = 0 To smartTagList.ListCount - 1
        If InStr(1, smartTagList.List(index, 0), searchBox.Value, vbTextCompare) > 0 Then
            index = index + 1
        ElseIf InStr(1, smartTagList.List(index, 1), searchBox.Value, vbTextCompare) > 0 Then
            index = index + 1
        ElseIf InStr(1, smartTagList.List(index, 2), searchBox.Value, vbTextCompare) > 0 Then
            index = index + 1
        Else
            smartTagList.RemoveItem (index)
        End If
    Next n

End Sub

Private Sub smartTagList_Click()

    If smartTagList.ListIndex > -1 And smartTagList.Selected(smartTagList.ListIndex) Then

        Dim smartyTag As String
        smartyTag = smartTagList.List(smartTagList.ListIndex, 2)

        Selection.Range.Text = smartyTag

    End If

    Unload Me

End Sub

Private Sub UserForm_Initialize()

    generateList

End Sub

Private Sub generateList()

    'replace with code to get values from database

    Dim fields() As String
    Dim descriptions() As String
    Dim smartyTags() As String

    fields = Split("Producer Name,Producer Address,Producer City,Producer State,Producer Zip,Insured Name,Insured Address,Insured City,Insured State,Insured ZIp,Risk Premium,TRIA Premium,Final Premium", ",")
    descriptions = Split("Name of Producer,Address Line of Producer,City of Producer,State of Producer,Zip Code of Producer,Name of Insured,Address of Insured,City of Insured,State of Insured,ZIp of Insured,Total Premium for all risks excluding terrorism taxes and surcharges.,Premium resulting from acceptance of terrorism protection,Total Premium of quote / policy", ",")
    smartyTags = Split("{$Producer Name},{$ProducerAddress},{$ProducerCity},{$ProducerState},{$ProducerZip},{$InsuredName},{$InsuredAddress},{$InsuredCity},{$InsuredState},{$InsuredZIp},{$RiskPremium},{$TRIAPremium},{$FinalPremium}", ",")

    Dim i As Long
    For i = LBound(fields) To UBound(fields)

        With smartTagList

            .AddItem
            .List(i, 0) = fields(i)
            .List(i, 1) = descriptions(i)
            .List(i, 2) = smartyTags(i)

        End With

    Next

End Sub

This is what my VBE looks like with .dotm open.

enter image description here

Solution

  • The most likely reason for the error message is that the macro is not available in the template containing the Ribbon customization. Often, they remain in the Normal.dotm template on the development machine.

    Background:

    Something the VBA developer always needs to keep in mind when starting out on a project and testing in Word is that Word works with multiple "contexts". First, foremost and the default when recording macros and creating functionality in the private user environment is the Normal.dotm template. This is "universal": it comes into play no matter in which document you work, even if that document is attached to a different template.

    THe great danger in this is that you may forget to move code meant for a specific template from Normal.dotm to that template. Or, even if you do copy the code over, if code with the same procedure names is present in both templates, that in Normal.dotm may keep precedence.

    This is especially a problem when assigning macros to Ribbon and QAT buttons. Even if Normal.dotm from the developer machine is no longer present, the Ribbon or QAT assignment still looks for that and doesn't check for an alternate source.

    So if you decide you want to keep the code in Normal.dotm as well as in the specific template it's a good idea to rename the procedures in Normal.dotmto avoid conflicts.