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