vba - How do I programmatically protect a Word Document from a UserForm CommandButton?

Question

I have a MS Word document which is a 10 question assessment made up of a table at the top containing details such as your name and table sections below, each for 1 question and answer and as required bookmarks. The basic gist of the document is;

1. On open, the document 'hides' and displays a UserForm to complete the required questions, including your name (Which is shown in the below sample code).
2. The UserForm is 'locked' where the only way to close it is to force close MS Word OR to click a command button and enter the correct password.
3. Navigation of the form is achieved with 'next' and 'previous' command buttons.
4. At the end of the form is a 'Submit' button (code shown below) which when clicked the user is prompted to confirm they are ready to submit the assessment, and if 'Yes' the form writes the values from the UserForm controls to a relevant bookmark in the document, replaces some of the bookmarks with enclosing bookmarks, saves the document, composes an email and attaches the document ready for sending, then resizes the window and closes MS Word.

In short, the above works perfectly, however after testing some users managed to double up some of their answers.

As the users are in various locations around the country, it can be difficult to get in touch (and trying to work out how things happened can be difficult with some users) so I've come to the conclusion the most logical reason would be:

• The User has not 'enabled content' and have completed the document only to then 'enable content' and be forced to complete the assessment via the UserForm, thus on submission adding another answer to the bookmark locations.

So to overcome this I've included the code to Protect the Document from editing.

It protects and unprotects as intended in various blocks of code for example the override used by admins to close the userform, generally for marking and when the document opens (with macros enabled of course) or when the Userform terminates. However the line to Protect the Document before it saves on submission does not seem to work (for lack of a better term).

It's preferred that the document is protected when it's opened in all instances. As the document is distributed with protection enabled, at first it works perfectly however after submission the saved version can be opened and edited without the UserForm if macros are not enabled (if prompted).

This code is a shortened version (omitting 19 variables, 15 Bookmark references and 4 enclosing bookmark replacements). We'll Assume for the example that to protect/unprotect the sheet, the password is "abc123".

Private Sub cmdSubmit_Click()

Dim confirm As Integer

confirm = MsgBox("Have you checked all your answers are correct?" & vbNewLine & vbNewLine & "By clicking 'Yes' you are confirming your completion of this Assessment", vbYesNo, "Submission Confirmation")

        If confirm = vbNo Then
            Exit Sub
        ElseIf confirm = vbYes Then
            MsgBox "A new email will open with this document attached." & vbNewLine & vbNewLine & "Please click send and set the security status to 'Un-classified'", vbInformation, "For Your Information"

Dim yourName As String

    yourName = UserForm1.TextBox1.Text

    If Not ActiveDocument.ProtectionType = wdAllowOnlyReading Then
        ActiveDocument.Protect wdAllowOnlyReading, , "abc123"
    End If

    ActiveDocument.Unprotect "abc123"

        ActiveDocument.Bookmarks("name").Select
            With Selection
            .TypeText Text:=yourName
            End With

        ActiveDocument.Bookmarks("name").Select
            With Selection
            .MoveEnd Unit:=wdLine, Count:=1
            .Bookmarks.Add name:="name"
            End With

End If

    ActiveDocument.Protect wdAllowOnlyReading, , "abc123"

    ActiveDocument.SaveAs2 FileName:="H:\Assessment 1_" & yourName, FileFormat:= _
        wdFormatXMLDocumentMacroEnabled, LockComments:=False, password:="", _
        AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
        EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
        :=False, SaveAsAOCELetter:=False, CompatibilityMode:=14

'Some code executes here to attach the saved document to a new outlook mailitem ready for sending. 

    Application.WindowState = wdWindowStateNormal
    Application.Resize 600, 700
    Application.Quit

End Sub

I've stepped through the code and the line is executed. To my understanding the whole code is in logical order and I don't see any reason it wouldn't either protect the document or when re-opened, open with protection enabled as it's been saved after protection has been set.

I have a feeling it's either something I haven't yet learned in Word VBA or something regarding the SaveAs code is not saving the protection, any thoughts?

标签： vbams-worduserform

Answer 1

您正在应用完整的只读保护。但是当您 SaveAs 时，您正在更改文档名称和文档类型，从不支持宏到支持宏。这使“只读”状态无效。所以你需要在保存后再次保护，然后进行“普通”保存。

如果您要对表单实施保护而不是“只读”，则可以避免这种情况。由于您没有在文档中设置任何可编辑区域，因此对于任何一种保护类型，我都可以看到您使用的没有任何区别......

doc.Protect wdAllowOnlyFormFields, True, "abc123"

以下是我的测试代码，对您发布的内容进行了优化。我所做的一项重要更改是在发出 Unprotect 命令（如果有的话）之前检查保护类型，这是您最初遇到的问题。我使用一个Document对象，而不是重复ActiveDocument，因为用户可能会以某种方式更改文档。我使用对象而不是Selection书签。

Public Sub cmdSubmit_Click()

    Dim confirm As Integer
    Dim yourName As String
    Dim doc As Word.Document
    Dim rngBookmark As Word.Range

    Set doc = ActiveDocument
    confirm = MsgBox("Have you checked all your answers are correct?" & vbNewLine & _
              vbNewLine & "By clicking 'Yes' you are confirming your completion of this Assessment", _
              vbYesNo, "Submission Confirmation")

    If confirm = vbNo Then
        Exit Sub
    ElseIf confirm = vbYes Then
        MsgBox "A new email will open with this document attached." & vbNewLine & _
               vbNewLine & "Please click send and set the security status to 'Un-classified'", _
               vbInformation, "For Your Information"
        yourName = UserForm1.TextBox1.Text

        If doc.ProtectionType <> wdNoProtection Then
            doc.Unprotect "abc123"
        End If
        Set rngBookmark = doc.Bookmarks("name").Range
        rngBookmark.Text = yourName
        doc.Bookmarks.Add Name:="name", Range:=rngBookmark

    End If

    doc.Protect wdAllowOnlyReading, , "abc123"

    doc.SaveAs2 fileName:="c:\Test\Assessment 1_" & yourName, FileFormat:= _
        wdFormatXMLDocumentMacroEnabled, LockComments:=False, Password:="", _
        AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
        EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
        :=False, SaveAsAOCELetter:=False, CompatibilityMode:=14

    'Saving to a different name, in a different file type, annuls the read-only protection
    'so protect again
    doc.Protect wdAllowOnlyReading, , "abc123"

'Some code executes here to attach the saved document to a new outlook mailitem ready for sending.

    Application.WindowState = wdWindowStateNormal
    Application.Resize 600, 700
    Application.Quit
End Sub