Error message about cell I'm trying to change being protected, not there when step thru

[9tanstaafl9]

I have a macro that worked fine until I did some things to speed it up. Now I get an error message (after the macro finishes running) that says: THE CELL OR CHART YOU ARE TRYING TO CHANGE IS PROTECTED... If I click ok and look at my workbook, everything is perfect. Macro ran fine. There is no cell with missing data.

I tried to isolate what line it was coming from, but the error never appears when you step thru the macro.

I can eliminate the problem by disabling the line of code at the end where I reprotect all the sheets, problem is that I need them protected.

Any ideas what might cause this?

I tried adding a 20 second wait before the protection line (report takes about 3 seconds to run), and that didn't help.


Here is the part where it breaks: (but ran fine on the slower version)

Private Sub ProtectAllSheets()

Dim myCount As Integer
Dim i As Integer
myCount = Application.Sheets.Count

On Error Resume Next

For i = 1 To myCount
 Worksheets(i).Protect Password:="ma$terbuilder", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowFiltering:=True
If i = myCount Then
Worksheets("Notes").Unprotect Password:="xxxxxx"
GoTo EndNow
End If
Next i

EndNow:

End Sub

Which is referenced in this section:

Private Sub Finished()
    Range("ContractName").Activate
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollColumn = 1
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.Run "ProtectAllSheets"
    Application.ScreenUpdating = True
    ActiveWorkbook.Protect Password:="xxxxxxxx"
    Application.Cursor = xlNormal
    MsgBox ("Finished.")
End Sub

The error window pops up after the msgbox.

I appreciate any suggestions. Don't spend too long on this if you don't just know the answer off the top of your head. What the people are using now takes them a few days to calculate and they won't really mind the error message, it just drives me nuts not knowing why it is happening.

 

[9tanstaafl9]

By the way this doesn't make any sense to me unless you are making a manual action on one of the protected sheets when the code has completed or if your last line is something like ActiveSheet.Range ("A1").Select and the active sheet at the end of the code is one of the protected sheets but that should have triggered when you stepped through the code.

Of course you are right, I didn't mean the VERY end. I put the breakpoint right before the msgbox "Finished". Sorry, I forgot for a moment that the members of this group are somewhat more literal than the people I normally hang out with. Don't get me wrong, I prefer it this way, I'm just not used to it.

And the sheet IS protected at that time, and it doesn't trigger when you step through the code, no matter what. The point is moot now, because using real data instead of test data has eliminated the problem. The report takes like 2 minutes to run, and it doesn't generate that error any more. I suppose there could be some fundamental difference in the data causing the fix, but I suspect it is more likely a timing issue.

Thanks again for everyone's input!