Password & WriteResPassword provided in VBA code but workbook still asking for password on open for some users

Jake Peralta

New Member
Jun 9, 2020
Office Version
  1. 2010
  1. Windows
I have a macro that opens a separate workbook ("Master File Test.xlsm") and pastes data into it, saves it, then closes it. in the General Options sections under Save As, the Master File has a Password to modify, does NOT have a password to open, and is NOT set to Read-only recommended. Simple enough, works fine for me, and works for 2 of the 4 people I've sent it to for testing.. For the other 2 though, they still get a password prompt i.e. ("Master File Test.xlsm is reserved by Jake Peralta Enter password for write access, or open read only.") which prevents the macro from proceeding, despite the fact that the code (below) clearly specifies the password.. I've even gone overboard and tried including the Password and WriteResPassword to no avail. Has anyone ever encountered this, or better yet, does anyone know why this is happening and how to resolve it?!

Thanks in advance!

VBA Code:
If bIsBookOpen_RB("Master File Test.xlsm") Then
        Set DestWB = Workbooks("Master File Test.xlsm")
        Set DestWB = Workbooks.Open(ThisWorkbook.Path & "\Master File Test.xlsm", , , , "Per@lta99", "Per@lta99")
    End If
    'For the next bit, make sure that in the Import Export tab that the export date format is set to GENERAL. Otherwise VBA will send dates that are ambiguous e.g. 07/12/2020 to the Master File as 12/07/2020 even if you have all your date formatting set to Australian/UK. Also make sure that Transition Formula Entry is not selected under File>Options>Advanced otherwise this will parse the data and convert it to a string that Excel won't recognise as a date when you try to filter.
Set SourceRange = ThisWorkbook.Sheets("Import Export").Range("B10:HO10")
With ThisWorkbook.Sheets("Import Export")

    LRow = .Cells(Rows.Count, 1).End(xlUp).Row
    varData = .Range("B10:HO10" & LRow)
End With
With DestWB.Sheets("Master Data")
    For i = LBound(varData) To UBound(varData)
        Set c = .Range("B:B").Find(varData(i, 1), LookIn:=xlValues, LookAt:=xlWhole)
        If c Is Nothing Then
            .Cells(Rows.Count, 2).End(xlUp)(2).Resize(1, UBound(varData, 2)) _
                = Application.Index(varData, i)
            .Cells(c.Row, 2).Resize(1, UBound(varData, 2)) _
                = Application.Index(varData, i)
        End If
End With
DestWB.Close savechanges:=True

    With Application
        .ScreenUpdating = False
        .EnableEvents = True
    End With

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Watch MrExcel Video

Forum statistics

Latest member