Macro bombs out after opening file

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
156
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?

VBA Code:
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

Board Regular
Joined
Feb 18, 2020
Messages
206
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
156
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

Board Regular
Joined
Feb 18, 2020
Messages
206
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?

VBA Code:
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
 

Forum statistics

Threads
1,148,327
Messages
5,746,128
Members
423,994
Latest member
blzxatly

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