Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel prompts for VBA password when opening
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel prompts for VBA password when opening
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel prompts for VBA password when opening
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" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel prompts for VBA password when opening
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" wrote in message ... 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" wrote in message ... 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel prompts for VBA password when opening
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" wrote in message ... 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" wrote in message ... 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 . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel prompts for VBA password when opening
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 "OMER" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Password Prompts for Links | Excel Discussion (Misc queries) | |||
Opening excel spreadsheets via excel.application object w/o prompts | Excel Worksheet Functions | |||
how to automate opening a password protected excel file? e.g. a .xls that has a password set in the security tab. | Excel Worksheet Functions | |||
Excel 2003 prompts for the password when I am not opening the stated file. | Excel Programming |