VBA coding in Word 2013

Question

I am having an issue regarding VBA in Word 2013. I have limited experience with coding, and macros are a bit of new territory for me.

I have a successful VBA code from a worker that no longer works in my office, and this code allows for a drop down menu in the office forms, allowing the user to choose their location and the footer changes the text address of the office in a string.

What I have been trying to do is to tweak this code so that on choosing the location, instead of showing text at the bottom of the page, the header template will change. I have successfully recorded the macros so it will do what I want on my computer, but when I try to share it with others, a few things happen. The drop down menu doesn't appear, then I have to put in the Developer tab. After that I have to unlock the document each time I want to run the macro (the old documents did not require this even though the old documents are also locked), then I get the error code saying the requested member does not exist, pointing to my recorded macro.

I'm sure I am doing something wrong but I'm unsure what that is. Some help would be greatly appreciated.

Option Explicit
Sub AutoNew()
Dim Mybar As CommandBar
Dim myControl As CommandBarComboBox
Dim cmd As CommandBar
Dim cmdyes As Integer
cmdyes = 0

For Each cmd In CommandBars
If cmd.Name = "Select Location" Then
cmdyes = 1
Exit For
Else
End If
Next

If cmdyes = 1 Then
CommandBars("Select Location").Visible = True
Else
Set Mybar = CommandBars _
.Add(Name:="Select Location", Position:=msoBarFloating, _
Temporary:=False)
Set myControl = CommandBars("Select Location").Controls _
.Add(Type:=msoControlDropdown, Before:=1)
With myControl
.AddItem " South Portland"
.AddItem " Bangor"
.AddItem " Presque Isle"
.ListIndex = 1
.Caption = "Select Office Location"
.Style = msoComboLabel
.BeginGroup = True
.OnAction = "processSelection"
.Tag = "AddresSelect"
    End With
End If

CommandBars("Select Location").Visible = True

If ActiveDocument.ProtectionType = wdAllowOnlyFormFields = False Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, 
Password:="password"
' ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, 
Password:=""
End If
End Sub
Sub AutoOpen()
Dim Mybar As CommandBar
Dim myControl As CommandBarComboBox
Dim cmd As CommandBar
Dim cmdyes As Integer
cmdyes = 0

For Each cmd In CommandBars
If cmd.Name = "Select Location" Then
cmdyes = 1
Exit For
Else
End If
Next

If cmdyes = 1 Then
CommandBars("Select Location").Visible = True
Else
Set Mybar = CommandBars _
.Add(Name:="Select Location", Position:=msoBarFloating, _
Temporary:=False)
Set myControl = CommandBars("Select Location").Controls _
.Add(Type:=msoControlDropdown, Before:=1)
With myControl
.AddItem " South Portland"
.AddItem " Bangor"
.AddItem " Presque Isle"
.ListIndex = 1
.Caption = "Select Office Location"
.Style = msoComboLabel
.BeginGroup = True
.OnAction = "processSelection"
.Tag = "AddresSelect"
    End With

End If

CommandBars("Select Location").Visible = True

If ActiveDocument.ProtectionType = wdAllowOnlyFormFields = False Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, 
Password:="password"
' ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, 
Password:=""
End If
End Sub
Sub processSelection()
Dim userChoice As Long
userChoice = CommandBars("Select Location").Controls(1).ListIndex
Select Case userChoice
    Case 1
        Call SoPortlandAddress
    Case 2
        Call BangorAddress
    Case Else
        Call PresqueIsleAddress
End Select
End Sub
Sub SoPortlandAddress()
'
' SoPortlandAddress Macro
'
'
If ActiveWindow.View.SplitSpecial <> wdPaneNone Then
    ActiveWindow.Panes(2).Close
End If
If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _
    ActivePane.View.Type = wdOutlineView Then
    ActiveWindow.ActivePane.View.Type = wdPrintView
End If
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
Templates.LoadBuildingBlocks
Application.Templates( _
    "C:\Users\bex172\AppData\Roaming\Microsoft\Document Building 
Blocks\1033\15\Building Blocks.dotx" _
    ).BuildingBlockEntries("South Portland Header").Insert Where:=Selection. 
_
    Range, RichText:=True
ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument

  If ActiveDocument.ProtectionType = wdAllowOnlyFormFields Then
ActiveDocument.Unprotect Password:="password"
End If
If ActiveDocument.ProtectionType = wdNoProtection Then
  FormLock
End If
End Sub

Sub BangorAddress()
'
' BangorAddress Macro
'
'
If ActiveWindow.View.SplitSpecial <> wdPaneNone Then
    ActiveWindow.Panes(2).Close
End If
If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _
    ActivePane.View.Type = wdOutlineView Then
    ActiveWindow.ActivePane.View.Type = wdPrintView
End If
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
Templates.LoadBuildingBlocks
Application.Templates( _
    "C:\Users\bex172\AppData\Roaming\Microsoft\Document Building 
Blocks\1033\15\Building Blocks.dotx" _
    ).BuildingBlockEntries("Bangor Header").Insert Where:=Selection.Range, _
    RichText:=True
ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument

If ActiveDocument.ProtectionType = wdAllowOnlyFormFields Then
ActiveDocument.Unprotect Password:="password"
End If
If ActiveDocument.ProtectionType = wdNoProtection Then
  FormLock
End If
End Sub

Sub PresqueIsleAddress()
'
' PresqueIsleAddress Macro
'
'
If ActiveWindow.View.SplitSpecial <> wdPaneNone Then
    ActiveWindow.Panes(2).Close
End If
If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _
    ActivePane.View.Type = wdOutlineView Then
    ActiveWindow.ActivePane.View.Type = wdPrintView
End If
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
Templates.LoadBuildingBlocks
Application.Templates( _
    "C:\Users\bex172\AppData\Roaming\Microsoft\Document Building 
Blocks\1033\15\Building Blocks.dotx" _
    ).BuildingBlockEntries("Presque Isle Header").Insert Where:=Selection. _
    Range, RichText:=True
ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument

If ActiveDocument.ProtectionType = wdAllowOnlyFormFields Then
ActiveDocument.Unprotect Password:="password"
End If
If ActiveDocument.ProtectionType = wdNoProtection Then
  FormLock
End If
End Sub
Sub FormLock()
'
' ToggleFormLock Macro
' Macro created 1/27/2004 by name removed
'
If ActiveDocument.ProtectionType = wdAllowOnlyFormFields = False Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, 
Password:="password"
 'if a password is used, add the line below after a space above
 'Password:="myPassword"
Else


 'if a password is used, add a comma after
 'the last line and include the line below
 'Password:="myPassword"
End If
End Sub

Could you post the code where the error is occurring? Also, is the macro security higher on the other machine you're testing it on? — Jimmy Smith
It might be that the old macros were writing to a location that was unlocked, but the new macro is writing to a location that is locked — VB Did Nothing Wrong
Finally got it! I just changed the passwords and any information with names and stuff, but the rest of the code is the original. Thanks! — bex172
I'm not sure about the macro security of the other machines. This form is supposed to be shared through our whole office. I know if I say 'enable macros' on the other computers it will show it, but I'll get the error code when I try to run it. — bex172

Variatus Variatus · Accepted Answer · 2017-10-12T09:48:51

I found your code a little unwieldy and understand that it poses a problem for you. The abbreviated version below should be easier to understand and therefore easier for you to manage once you acquaint yourself with its own peculiarities. Note that I tested everything except the actual extraction and insertion of the building block, since you said it is working.

Option Explicit

    ' declare the name (so as to eliminate typos)
    Const CmdName As String = "Select Location"

Sub AutoNew()
    ' 12 Oct 2017
    SetCommandBar
End Sub

Sub AutoOpen()
    ' 12 Oct 2017
    SetCommandBar
End Sub

Sub SetCommandBar()
    ' 12 Oct 2017

    Dim MyBar As CommandBar
    Dim MyCtl As CommandBarControl
    Dim MyList() As String
    Dim Cmd As CommandBar
    Dim i As Integer

    ' delete the existing (so that you can modify it)
    For Each Cmd In CommandBars
        If Cmd.Name = CmdName Then
            Cmd.Delete
            Exit For
        End If
    Next Cmd

    ' in Word >= 2007 the commandbar will be displayed
    ' in the ribbon's Add-ins tab
    Set MyBar = CommandBars.Add(Name:=CmdName, _
                                Position:=msoBarFloating, _
                                MenuBar:=True, _
                                Temporary:=True)
    Set MyCtl = CommandBars(CmdName).Controls.Add( _
                                Type:=msoControlDropdown, _
                                Before:=1)

    ' Names must match Building Block names (without " Header")
    MyList = Split(" South Portland, Bangor, Presque Isle", ",")
    With MyCtl
        .Caption = "Select Office Location"
        .Style = msoComboLabel
        For i = 0 To UBound(MyList)
            .AddItem MyList(i)
        Next i
        .ListIndex = 1
        .OnAction = "SetHeader"
    End With
    CommandBars(CmdName).Visible = True
End Sub

Sub SetHeader()
    ' 12 Oct 2017

    Const BlockFile As String = "C:\Users\bex172\AppData\Roaming\Microsoft\" & _
                                "Document Building Blocks\1033\15\" & _
                                "Building Blocks.dotx"
    Dim BlockID As String

    SetFormLock False               ' not needed if the document isn't locked
    With ActiveWindow
        If .View.SplitSpecial <> wdPaneNone Then .Panes(2).Close
        With .ActivePane.View
            If .Type = wdNormalView Or .Type = wdOutlineView Then
                .Type = wdPrintView
            End If
            .SeekView = wdSeekCurrentPageHeader
        End With
    End With

    BlockID = Trim(CommandBars(CmdName).Controls(1).Text) & " Header"
    Templates.LoadBuildingBlocks
    Application.Templates(BlockFile).BuildingBlockEntries(BlockID).Insert _
                Where:=Selection.Range, _
                RichText:=True
    SetFormLock True                ' not needed if the document isn't to be locked
End Sub

Sub SetFormLock(ByVal FormLock As Boolean)
    ' 12 Oct 2017
    ' call this procedure with either "True" or "False" as argument
    ' to either lock or unlock the form.

    ' The same password is used for unlocking and locking.
    ' MAKE SURE THE DOCUMENT IS UNLOCKED before changing the password!
    Const Password As String = ""

    Dim Doc As Document

    Set Doc = ActiveDocument
    With Doc
        If .ProtectionType = wdNoProtection Then
            If FormLock Then
                 ' you can't set the protection while any other part of the document is active
                ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument

               ' you may wish to specify another type of protection:
                ' this code protects all except FormFields
                .Protect Type:=WdProtectionType.wdAllowOnlyFormFields, _
                         NoReset:=True, _
                         Password:=Password, _
                         UseIRM:=False, _
                         EnforceStyleLock:=False
            End If
        Else
            If Not FormLock Then .Unprotect Password
        End If
    End With
End Sub

Your question didn't allow full understanding of your problem. It might have to do with the location of the code itself or with the protection. By making the code more transparent I hope that you will either be able to eliminate the problem or find the right question to ask.

VBA coding in Word 2013

1 Answers