Macro bombs out after opening file

[melodramatic]

I have a macro that I've been using for some time, but it quit on me after my Office 365 update last Friday.

In troubleshooting, I added msgboxes everywhere to see what I was getting. The variables are being assigned correctly. I unprotected all sheets, just in case it didn't like working on protected sheets (although the cells with variables being enter are always unlocked).

Basically, as soon as the Test Binder Prep Macro file opens up (and it does open), everything quits.

Help?

Sub TestBinderPrep()
'
' This Macro calls up the Test Binder Prep macro
' Macro created 9/22/2017 by Melody October May
'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
If Left(Range("G2"), 3) = "XXX" Then
resp = MsgBox("Test Binder Prep cannot run " & vbNewLine & "without forms first being created." & vbNewLine & vbNewLine & _
"Please run the ''Complete Forms'' macro above, then you may return to the Test Binder Prep macro.", vbOKOnly, "Forms for Binder Prep do not exist")
Exit Sub
End If
If MsgBox("You will be opening the Test Binder Prep macro, to create Specimen & Test folders, as well as test documentation.", vbOKCancel, "Confirm opening file") = vbCancel Then
Exit Sub
Else
Dim macroPath As String
Dim PN As Long
Dim ConnType As String

PN = Range("B5")
If Left(Range("B13"), 3) = "XOM" Then
Protocol = "XOM"
Else
Protocol = "ISO / API"
End If
    ConnType = Range("B12")
   
If Left(PN, 3) = 520 Then
macroPath = "N:\In House Projects\9200006 - Administration\ISO TESTING Projects\MACRO\"
Else
macroPath = "P:\LAB\ISO TESTING Projects\MACRO\"
End If

Workbooks.Open Filename:=macroPath & "\Test Binder Prep Macro.xlsm", ReadOnly:=True

'THIS IS WHERE IT'S HANGING UP - NOTHING ELSE HAPPENS AFTER OPENING THE FILE (

Sheets("Input").Select

Range("F6").Value = PN
Range("K1").Value = Protocol
Range("K2").Value = ConnType

MsgBox "Please input the tests you'll be setting up, then press 'Create Binders'"

End If
End Sub

 

[CephasOz]

Hi melodramatic.

I see that when you open the workbook, you do so with "ReadOnly:=True". Then you try to change cells in its "Input" worksheet. Perhaps if you took off the read only?

 

[melodramatic]

Thanks Jim - I appreciate that you worked with me on this today

 

[melodramatic]

CephasOz - the readonly is to keep users from accidentally changing the Masterfile. The only issue would be not being able to save the readonly file, and I don't want them to

 

[CephasOz]

Of course.

As a principle, I have found the least troublesome way to write VBA is to be explicit, because it's easier to match your expectations to what Excel is actually doing. So I have edited your code to be so, plus included error handling, and restored the ScreenUpdating and DisplayAlerts values that existed before the macro was run. Is this useful to you?

Sub TestBinderPrep()
    '
    ' This Macro calls up the Test Binder Prep macro
    ' Macro created 9/22/2017 by Melody October May
    '
    Dim macroPath As String
    Dim PN As String
    Dim ConnType As String
    Dim wbkToUse As Workbook
    Dim wksInput As Worksheet
    Dim bolUpdating As Boolean
    Dim bolAlerts As Boolean
    Dim strMsg As String
    '
    On Error GoTo Err_Exit
    '
    bolUpdating = Application.ScreenUpdating
    bolAlerts = Application.DisplayAlerts
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    '
    With ActiveSheet
        If Left(.Range("G2").Value, 3) = "XXX" Then
            strMsg = "Test Binder Prep cannot run " & vbNewLine & "without forms first being created."
            strMsg = strMsg & vbNewLine & vbNewLine
            strMsg = strMsg & "Please run the ''Complete Forms'' macro above, then you may return to the Test Binder Prep macro."
            MsgBox strMsg, vbOKOnly + vbInformation, "Forms for Binder Prep do not exist"
        Else
            strMsg = "You will be opening the Test Binder Prep macro, to create Specimen & Test folders, as well as test documentation."
            If MsgBox(strMsg, vbOKCancel + vbDefaultButton1, "Confirm opening file") = vbOK Then
                PN = .Range("B5").Value
                If Left(PN, 3) = 520 Then
                    macroPath = "N:\In House Projects\9200006 - Administration\ISO TESTING Projects\MACRO\"
                Else
                    macroPath = "P:\LAB\ISO TESTING Projects\MACRO\"
                End If
                Set wbkToUse = Workbooks.Open(Filename:=macroPath & "Test Binder Prep Macro.xlsm", ReadOnly:=True)
                Set wksInput = wbkToUse.Sheets("Input")
                wksInput.Range("F6").Value = PN
                wksInput.Range("K1").Value = IIf(Left(.Range("B13").Value, 3) = "XOM", "XOM", "ISO / API")
                wksInput.Range("K2").Value = .Range("B12").Value
                wksInput.Activate
            End If
        End If
    End With
Housekeeping:
    Application.ScreenUpdating = bolUpdating
    Application.DisplayAlerts = bolAlerts
    Set wksInput = Nothing
    Set wbkToUse = Nothing
    Exit Sub
Err_Exit:
    strMsg = CStr(Err.Number) & ": " & Err.Description
    Err.Clear
    MsgBox strMsg, vbCritical + vbOKOnly, "TestBinderPrep"
    Resume Housekeeping
End Sub

 

[melodramatic]

Interesting changes. I'll look them over.