Macro to change multiple instances of password in a project?

[BAlGaInTl]

My spreadsheet currently uses VBA with .Protect Password:= to protect and unprotect different sheets for data entry by a userform.

Currently, if I want to change the password, I simply use the VBA editor and the Find/Replace option.

Someone else may be assigning passwords in the future, and I would like the option of a macro that would make things easier for them. The generic starting password is "QADPass"

A macro that asks for the old password, then allows you to input a new password that would replace all instances of QADPass would be great.

Any thoughts?

 

[Andrew Poulsom]

Re: Macro to change multiple instances of password in a proj

Try this:

Sub Test()
    Dim VBP As Object
    Dim PassOld As String
    Dim PassNew As String
    Dim Found As Boolean
    Dim x As Long
    Dim TextNew As String
    Set VBP = ThisWorkbook.VBProject
    PassOld = InputBox("Enter old password", "Old Password")
    If PassOld = "" Then
        Exit Sub
    Else
        PassOld = "password:=""" & PassOld & """"
    End If
    PassNew = InputBox("Enter new password", "New Password")
    If PassNew = "" Then
        Exit Sub
    Else
        PassNew = "password:=""" & PassNew & """"
    End If
    With VBP.VBComponents("Module1").CodeModule
        Found = .Find(PassOld, 1, 1, -1, -1, True, False)
        If Found = False Then
            MsgBox "Incorrect password"
        Else
            For x = 1 To .CountOfLines
                If .Find(PassOld, x, 1, x, -1, True, False) = True Then
                    TextNew = Replace(.Lines(x, 1), PassOld, PassNew)
                    .ReplaceLine x, TextNew
                End If
            Next x
        End If
    End With
End Sub

 

[BAlGaInTl]

Re: Macro to change multiple instances of password in a proj

No luck, I get the following error that points to this line.

Set VBP = ThisWorkbook.VBProject

Run Time error '1004'
Programatic Access to VisualBasic Project is not trusted

 

[Andrew Poulsom]

Re: Macro to change multiple instances of password in a proj

You will need to change your Macro security settings. Choose Tools|Macro|Security|Trusted Sources tab and check Trust access to the VB Project.