• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Trouble with Password Dialogue Box

T

The Doctor

Member
More questions for the Ninjas out there. I've got a forecast workbook that has been parsed down by region for updating my regional managers. This workbook includes the following script on open to pull up the master forecast and update the data on the regional workbook. Because the master forecast is protected to prevent regions from being privy to forecasting and budgeting information for other regions it is causing me some trouble. I have on each regions workbook the following VBA:


Private Sub Workbook_open()


Workbooks.Open Filename:="networkpathsubfoldermaster forecast.xlsx", Password:="Pass#Word"

Workbooks("master forecast.xlsx").Close SaveChanges:=False


End Sub


Yet each time their workbooks get opened they receive the password input dialogue box. Pressing cancel will still allow the links in the regional workbook to update, but I wan to prevent the password dialogue from opening.


I have also tried adding an Application.DisplayAlerts = False string, but still get the password dialogue. I've managed to baffle my IT folks on this one, so I'm looking to the Excel Ninja message board :)


Thanks,

The_Doctor
 
Hi,


The link is probably trying to update before the master workbook is opened by your code. Without trying to redesign your current workbook structure, you could disable the automatic update of the link and then add more code to your Workbook_Open() event handler to update the link after it has opened the master workbook.


That said, this process is a little flakey. If your code errors for some reason then the user could be left with the master workbook sitting open in front of them. If your user can see the code then they'll be able to see the password. If the data is sensitive then it might be worth considering getting it into a proper database and assigning appropriate permissioning to it.
 
There's an AutoOpen() event handler, but that gets called after Workbook_Open(). So you're already using the correct event handler from that perspective. The option I posted before should do the trick - did you implement it?
 
Disabling the automatic update is something you should do manually and then save the workbook. Once you've changed it to not automatically update and saved the workbook, it will keep that setting, so no need to do it the VBA. To do this go to the Data tab on the ribbon > Edit Links > StartUp Prompt > Don't display the alert and don't update automatic links.

What you do need to do in the VBA is update the link. I think this should do the trick but it is untested:

[pre]
Code: 
Private Sub Workbook_open()

Dim wkbMaster As Workbook

Set wkbMaster = Workbooks.Open(Filename:="networkpathsubfoldermaster forecast.xlsx", Password:="Pass#Word")

'this will update the link
UpdateLink LinkSources(wkbMaster.FullName), xlExcelLinks

wkbMaster.Close SaveChanges:=False

End Sub
[/pre]
 
Turns out I already had the automatic update links disabled. That one still brings up the password dialogue, and I can't cancel out of it without debugging. The Debuggger indicates error on line UpdateLink LinkSources(wkbmaster.FullName), xlExcelLinks.
 
I guess the error is because of the network server in the filepath. Try this instead:

[pre]
Code: 
Private Sub Workbook_open()

Const strFILENAME As String = "networkpathsubfoldermaster forecast.xlsx"

Dim wkbMaster As Workbook

Set wkbMaster = Workbooks.Open(Filename:=strFILENAME, Password:="Pass#Word")

'this will update the link
UpdateLink LinkSources(strFILENAME), xlExcelLinks

wkbMaster.Close SaveChanges:=False

End Sub
[/pre]
You definitely have automatic update links disabled in the workbook containing the code?
 
Definitely.


This one doesn't give me the password dialogue, but immediately opens up a window to select a file..."Update Values:M" if I press Cancel the debugger returns, error in the same place. If I skip the updatelink string it returns an error on the close command as well.
 
Which version of Excel are you using? 2007 or 2010?


Try adding the network subfolder to your trusted locations in the trust center.
 
Did adding it to the trusted locations fix the problem then?


If it did, then yes, I guess they'll have to. It's not something which should be done programmatically (it could be exploited). To help, you could add some error handling to ensure that the master workbook isn't left open. Perhaps something like this:

[pre]
Code: 
Private Sub Workbook_open()

Const strFILENAME As String = "networkpathsubfoldermaster forecast.xlsx"

Dim wkbMaster As Workbook
Dim strMsg As String

On Error GoTo ErrorHandler

Set wkbMaster = Workbooks.Open(Filename:=strFILENAME, Password:="Pass#Word")

'this will update the link
UpdateLink LinkSources(strFILENAME), xlExcelLinks

wkbMaster.Close SaveChanges:=False

Exit Sub

ErrorHandler:

strMsg = Err.Description

On Error Resume Next
If Not wkbMaster Is Nothing Then wkbMaster.Close False

MsgBox "An unexpected error occured: " & strMsg & _
vbNewLine & vbNewLine & _
"Please ensure that " & strFILENAME & _
" is added to your trusted locations in the trust center."
End Sub
[/pre]
 
You must log in or register to reply here.
Back
Top