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

Jake Peralta

New Member
Joined
Jun 9, 2020
Messages
9
Office Version
  1. 2010
Platform
  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")
    Else
        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)
        Else
            .Cells(c.Row, 2).Resize(1, UBound(varData, 2)) _
                = Application.Index(varData, i)
        End If
    Next
End With
DestWB.Close savechanges:=True

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

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Watch MrExcel Video

Forum statistics

Threads
1,123,241
Messages
5,600,499
Members
414,383
Latest member
konmtu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top