VBA Error when un-protecting document, running sub, and protecting document (Ranges with if/thens don't seem to work but basic if then works)

user_16690680 0
Viewed 48

I have a user form that unprotects a document to let the info be entered into the doc and after it protects my document. All of my subs are set up and work asides the one which is giving me a problem:

Example of sub that works:

Private Sub ComboBox5_Change()
ActiveDocument.Unprotect "password"
    Dim ComboBox5 As Range
    Set ComboBox5 = ActiveDocument.Bookmarks("bmragpd").Range
    ComboBox5.Text = Me.ComboBox5.Value
    
    If Me.ComboBox5.Value = "No" Then
    ComboBox5.Text = "205.55a"

    End If
    
    If Me.ComboBox5.Value = "Yes" Then
    ComboBox5.Text = ""

    
    End If
    ActiveDocument.Protect wdAllowOnlyFormFields, NoReset:=True, Password:="password"
End Sub

For some reason this sub doesn't work and will say the document is already unprotected any suggestions on how to resolve this? I tried removing the unprotect on combobox6 however that doesn't seem to work either:

Private Sub ComboBox6_Change()
ActiveDocument.Unprotect "password"

   Dim rngComboBox6 As Range

Dim sssaText As String

Dim iiia As Integer

    Set rngComboBox6 = ActiveDocument.Bookmarks("bmfcs").Range

    sssaText = ComboBox6.Value

    If Me.ComboBox6.Value = "Yes" Then

        For iiia = 1 To 1

    sssaText = sssaText & Chr(13) & "200" _
    & Chr(13) & "200.1" _
    & Chr(13) & "" _
    & Chr(13) & "OEBS" _
    & Chr(13) & "" _
    & Chr(13) & "21c" _
    & Chr(13) & "" _
    & Chr(13) & "22c" _
    & Chr(13) & "Yes" _
    & Chr(13) & "" _
    & Chr(13) & "Yes" _
    & Chr(13) & "Two" _
    & Chr(13) & "" _
    & Chr(13) & "ES2a.1" _
    & Chr(13) & "" _
    & Chr(13) & "222" _
    & Chr(13) & "" _
    & Chr(13) & "222a" _
    & Chr(13) & "222b" _
    & Chr(13) & "" _
    & Chr(13) & "3.a.1" _
    & Chr(13) & "" _
    & Chr(13) & "NA" _
    & Chr(13) & "" _
    & Chr(13) & "I. TuuVa"
 
    Next iiia

    sssaText = sssaText & Chr(13) & "717217" _
    & Chr(13) & "" _
    & Chr(13) & "1212" _
    & Chr(13) & "" _
    & Chr(13) & "D.1" _
    & Chr(13) & "F2B-4"

    End If
            
    rngComboBox6.Text = sssaText

    ActiveDocument.Bookmarks.Add "bmfcs", rngComboBox6
    
     If Me.ComboBox6.Value = "No" Then
    ComboBox6.Text = ""

    End If
    
    ActiveDocument.Protect wdAllowOnlyFormFields, NoReset:=True, Password:="password"
End Sub
userform ms-word combobox vba
1 Answers

The first End If is in the wrong place. You are adding text to the document regardless of the value of the combo box.

It is not good practice to use a control’s change event to commit changes to a document. Apart from anything else it doesn’t allow the user to cancel without making changes.

Instead use an OK/Apply/Finish button.

Then you only need to unprotect/reprotect the document once.

user_52117520
Related