Excel prompts for VBA password when opening
in [Programming]
|
From: OMER on 1 Feb 2010 12:10 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
From: Peter T on 1 Feb 2010 12:51 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" <OMER(a)discussions.microsoft.com> wrote in message news:D4073820-2E00-4912-9DCF-AD83A57B8531(a)microsoft.com... > 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 >
From: OMER on 1 Feb 2010 15:10 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" wrote: > 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" <OMER(a)discussions.microsoft.com> wrote in message > news:D4073820-2E00-4912-9DCF-AD83A57B8531(a)microsoft.com... > > 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 > > > > > . >
From: Peter T on 1 Feb 2010 15:28 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" <OMER(a)discussions.microsoft.com> wrote in message news:9351BFFB-6924-4ED8-893D-7FBB85A826EE(a)microsoft.com... > 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" wrote: > >> 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" <OMER(a)discussions.microsoft.com> wrote in message >> news:D4073820-2E00-4912-9DCF-AD83A57B8531(a)microsoft.com... >> > 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 >> > >> >> >> . >>
From: OMER on 1 Feb 2010 16:26
Thanks again Peter. I'll follow your recomendations and test your code. I'll let you know about the results. Regards, OMER "Peter T" wrote: > 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" <OMER(a)discussions.microsoft.com> wrote in message > news:9351BFFB-6924-4ED8-893D-7FBB85A826EE(a)microsoft.com... > > 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" wrote: > > > >> 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" <OMER(a)discussions.microsoft.com> wrote in message > >> news:D4073820-2E00-4912-9DCF-AD83A57B8531(a)microsoft.com... > >> > 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 > >> > > >> > >> > >> . > >> > > > . > |