I am opening a whole bunch of workbooks that all have the same password in order to check their sheets.
The program starts with a dummy password that will fail, and when it does a userform (named
PasswordUserform) is shown in order to capture the password for all of the files. Subsequently, each workbook is opened via that password. As such, it should A) never fail, and B) never use the default password entry userform.
Private Function CheckFiles(ByRef WkbApp As Excel.Application, _ WkbFolder As String, _ FoundFile As String, _ NeededSheets As Scripting.Dictionary, _ ByRef Pass As String) As Boolean Dim Wkb As Workbook WkbApp.Visible = False ' Attempts to open the workbook. If it fails, then attempts to get a ' better password from the user. This password is saved for further ' attempts, but only during runtime. On Error Resume Next Set Wkb = WkbApp.Workbooks.Open(Filename:=WkbFolder & FoundFile, _ Password:=Pass) If Err.Number > 0 Then PasswordUserform.FileNameLabel.Caption = "'" & _ Left(FoundFile, 20) & "...'" PasswordUserform.Show Pass = PasswordUserform.PasswordBox.Text Unload PasswordUserform Set Wkb = WkbApp.Workbooks.Open(Filename:=WkbFolder & FoundFile, _ Password:=Pass) End If On Error GoTo 0 ' Do Stuff within workbook, Setting function to True or False depending on contents End Function
The function is called within a simple Do While loop:
Do While Len(FoundFile) > 0 FoundDate = FileDateTime(WkbFolder & FoundFile) If FoundDate > LatestDate And CheckFiles(WkbApp, WkbFolder, _ FoundFile, NeededSheets, Pass) Then LatestDate = FoundDate LatestFile = FoundFile LatestPass = Pass End If FoundFile = Dir Loop
When stepping through, the first
Set Wkb may or may not display the default password userform. If that pops up and is cancelled, the custom
PasswordUserform displays. On proper entry, the second
Set Wkb (in the IF scope) always displays the default password userform. But on the next file, the password is used properly and no userform is displayed. Finally, the occasional file will create the default password userform (on inputting the password that should have been provided, it opens fine, as all files in the folder use the same password).
I’ve tried setting the
WriteResPassword as well, with no difference. Additionally,
DisplayAlerts=False does not prevent the problem.