I've been using code in a worksheet several times every day for over a year and it has always worked until I made what I thought was a simple change. Now the code gives me a "Method "Close" of object '_Workbook' failed". I've checked to make sure I have DoEvents and Application.DisplayAlerts = False at all the appropriate locations within the code.
My main code opens text files and copies the data into the main workbook into various worksheets within the main workbook, then closes the text files. I have a userform as a progress indicator that displays a label and check box as each step of the code is completed. But I wasn't using the progress indicator beyond a specific location in the code. I wanted to expand the progress indicator to the balance of the code. I didn't change anything except to copy the progress indicator call routine into the various places within the code. Now Workbooks.Close and Workbooks.Activate give the error message, but only from the point where I recently inserted the progress indicator call routine. Everything prior to that location in the code works just as it previously did.
I know it has to be something simple, but I’m missing it. Here’s a sample of my code.
Public Sub ProgressList(StepProg As Single, StepDesc As String)
ufAuditFormProgList.Controls("Label" & StepProg).Caption = StepDesc
DoEvents
End Sub
Public Sub ProgLogStat(StepLog As Single)
ufSetupLogProgList.Controls("CheckBox" & StepLog).Value = True
For T = 1 To 5000 'adds a pause between steps
Next T
DoEvents
End Sub
Sub CloseTextFiles()
Call ProgressList(StepProg, "Closing Text Files")
Application.DisplayAlerts = False
Workbooks("zSubK Amounts.txt").Close
Workbooks("zExported PFR.txt").Close
Workbooks("zECI_SubKs.txt").Close
Workbooks("zChgItems.txt").Close
Workbooks("Job_info.txt").Close
'update progress indicator check box
Call ProgressStat(StepProg)
StepProg = StepProg + 1
End Sub