Adding references from VBA Causing Password Prompt To Appear

Question

EDIT: I WILL GIVE A 300 rep BOUNTY FOR THIS:-)

I have run out of ideas. I have a very simple macro that adds references.

It looks like I have the same problem as this http://www.eggheadcafe.com/software/aspnet/35651964/excel-prompts-for-vba-pas.aspx

Sub testAddSolver()
    Call AddSolver()
End Sub


Sub AddSolver()
    Dim strSolverPath As String
    Dim wbSolver As Workbook
    Dim objRef As Object
    Dim oWB As Object    ' NOT as workbook
    Dim ad As AddIn

    On Error GoTo errH
    Set oWB = ActiveWorkbook

    With Application.AddIns("Solver Add-In")
        strSolverPath = .FullName
        On Error Resume Next
        Set wbSolver = Workbooks(.name)
        On Error GoTo errH

        If wbSolver Is Nothing Then
        .Installed = True
        End If
    End With

    On Error Resume Next
    Set objRef = oWB.VBProject.References("SOLVER")
    On Error GoTo errH
    If objRef Is Nothing Then
        Call MsgBox(strSolverPath)
        oWB.VBProject.References.AddFromFile strSolverPath
    End If

    Call MsgBox("Compleetd")
    Exit Sub
errH:
    MsgBox Err.Description, , "Error in AddSolver"
End Sub

This adding references works fine until I put a password around the VBA at which point it prompts me for a password.

I have tried absolutely everything

Answer 1

If you uncheck the "Lock project for viewing" checkbox, you won't get a prompt when your code tries to set a reference. But then I'm not sure what the point of the protection would be.

Sadly, I think you're sunk. If you want to add a reference programatically, you'll need access to the code.

Answer 2

You password protected your VBA project. Therefore, any changes to that project require it to be opened, so you get the password prompt.

You can try and create an Add-In to work around this problem. It may work because you are creating a DLL/XLA from the VBA code, so you should be able to add references.

XLA

Save the workbook that contains your code as "Microsoft Office Excel Add-In (.xla)"

DLL

To create the add-in you need the Office XP Developer. Microsoft states this tool is not supported for newer versions of Office, but those versions all use VBA/VBE v6.x. I never had any problems with these tools in Office 2003.

Note, if you expect ActiveWorkbok to point to your code then you will have to adjust these statements when working from an add-in.