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.

 

[MARK858]

When you run the code and you go to Worksheets("Notes") is it unprotected?

 

[9tanstaafl9]

When you run the code and you go to Worksheets("Notes") is it unprotected?

Yes, it is.

And the active cell is the one it is supposed to be from the Finished Sub

The last "difficult" thing the macro did before the code I mentioned was to add subtotals and format those rows, and that also worked perfectly.

 

[MARK858]

I take it when you tested for the error message you did remove / comment out the "On Error Resume next" line?

 

[9tanstaafl9]

Well, that would have been a smart thing to do. I should have done that. I diid that just now, and it didn't change the outcome in any way. Then I got rid of ALL the on error resumes in my macro and I found a different error. (The part where it is adding the subtotals, which is code I don't really understand very well, someone on this board had given it to me.) I need to put my daughter to bed but I will investigate it more and post in the morning.

'REMOVES BLANK ROWS alter when adding rows or columns (what is 17?)
        
        Range(Range(Split(Range("FilteringColumn").Address(1, 0), "$")(0) & 17), Range(Split(Range("FilteringColumn").Address(1, 0), "$")(0) & Rows.Count).End(xlUp)).AutoFilter Field:=1, Criteria1:="TRUE" 'Range row 17 for filter column
        Dim CountRange As Long
        CountRange = Range(Range(Split(Range("FilteringColumn").Address(1, 0), "$")(0) & 17), Range(Split(Range("FilteringColumn").Address(1, 0), "$")(0) & Rows.Count).End(xlUp)).Rows.Count - 1
        'On Error Resume Next
        Range(Range(Split(Range("FilteringColumn").Address(1, 0), "$")(0) & 17), Range(Split(Range("FilteringColumn").Address(1, 0), "$")(0) & Rows.Count).End(xlUp)).Resize(CountRange, 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete <--BREAKS HERE
        ActiveSheet.AutoFilterMode = False
        If Range(Split(Range("FilteringColumn").Address(1, 0), "$")(0) & 17) = "True" Then Range(Split(Range("FilteringColumn").Address(1, 0), "$")(0) & 17).EntireRow.Delete

I don't know if this is relevant to the current issue or not, just posting in case it is something obvious to someone else. Not trying to start a different thread. Error was just a runtime 1004.

 

[9tanstaafl9]

OK, that was a red herring being caused by the fact that there were actually no TRUE rows in the job I ran. I fixed that.

So I did a step thru with all the On Error Resume Next lines commented out, and there were no errors when stepping through.

But I still get the error if I run the macro using the button on the sheet. (Again, the macro appears to run successfully.)

I'm ready to give up. I just wanted to let you guys know that the error mentioned previously wasn't the issue.

Thanks for all your help!

 

[MARK858]

Stick break points in about every 10 rows till the error message appears so you have narrowed it down then put more break points within that area until you find the actual line causing the error.

 

[9tanstaafl9]

It won't generate the error if you have breakpoints. If I put just one breakpoint at the very end, it runs fine.

Anyway, the problem appears to be moot at this point (even though I am dying of curiosity). Apparently when the users run this over their network it goes slow enough that the error doesn't appear.

Anyway, thanks for all your help!

 

[MARK858]

You run the code with the button removing each breakpoint as it doesn't produce the message, if it was producing the message before with the button then it should still produce it with the breakpoints.

Even though I don't like the idea if you sure it is producing the desired result then you could put

Application.DisplayAlerts = False

at the start of your code and change it to true at the end of your last sub which should suppress the message.
Like I stated though it is not a good way around it.

 

[MARK858]

If I put just one breakpoint at the very end, it runs fine

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.