Excel prompts for VBA password when opening

[OMER]

Hola,
I have a workbook in which I protected the VBA code with a password. It
works fine. The user doesn't have access to the code. I'm automatically
setting the Solver referece using the following code (partly shown).

Set oWB = ActiveWorkbook
strSolverPath = Application.LibraryPath & "\SOLVER\SOLVER.XLAM"
With AddIns("Solver Add-In")
.Installed = False
.Installed = True
End With
oWB.VBProject.References.AddFromFile strSolverPath


The situation is that when the user opens the workbook it's being asked for
the VBA Project password. The user clicks Cancel and continue without the
need to know the password.

Is there a way to prevent the code to prompt for password?
(I want to keep the code password protected).

Thank you for your help

 

[Peter T]

The code may be breaking due to an error that only occurs in the users
setup. Looking at yours it is prone to all sorts of errors, eg
user's security settings do not allow access to VB Project.
the workbook already has the reference
the addin is not in the expected location

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 errror 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 errro GoTo errH
If objRef Is Nothing Then
oWB.VBProject.References.AddFromFile strSolverPath
End If

Exit Sub
errH:
MsgBox Err.Description, , "Error in AddSolver"
End Sub

Of course there might be some other error.

Regards,
Peter T

 

[OMER]

Thank You Peter T for you prompt response.
Definetively your code is better developed that mine.
However, mine is not sending any error code.
If I disable the VBA password protection, it runs smoothly. The issue arises
when I enabled the VBA password. The workbook prompts for the VBA password.
If I click cancel (to ignore the passowrd message) it continues to run
without a problem and correctly sets the Solver reference.

I believe it has to do with setting the Solver reference while the password
protected, but I want to avoid the password prompting.

Any more ideas?
Regards,
OMER

 

[Peter T]

So you are saying it all works on *user's* machine if the project is not
disabled. Did you check these

- Do user's settings enable access to VB Project
- Are you sure the reference is not already set in the workbook

Either of these would definitely cause the password prompt, unless the code
is along the lines I posted.

Setting the Solver reference shouldn't trigger the password prompt unless
there's an error, and not all errors can be handled nicely. Why not try a
test workbook with only the code to set the reference, try locked and
unlocked.

Regards,
Peter T

PS - did you try with the code I suggested ?

 

[OMER]

Thanks again Peter.
I'll follow your recomendations and test your code. I'll let you know about
the results.
Regards,
OMER

 

[Peter T]

Don't forget with my code the Solver Addin is only installed if not already
and the reference only added if not already added.

For testing, my code, might be an idea to ensure the Solver Addin is not
installed and the reference does not yet exist. Otherwise you might not get
the full picture

Regards,
Peter T