Accidentally Protecting Sheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a macro which checks to see if any of my worksheets are protected
prior to running a larger macro. It has been flagging protected sheets that
I am not knowingly protecting.

I set up a Watch using the line: Cell.Parent.Protect = True.

It breaks on the first line of the Public Function. I have stepped through
it and it seems to protect the sheet on the line:

ElseIf CellIsHidden(Cell) And ProtectedCellsChkBx = False Then.

Why is this happening with the IF argument? Everytime I run this I have to
unprotect my sheets. What even odder is that in my immedaite window I have
typed in the same term as used for my watch (?Cell.Parent.Protect = True),
and it equals TRUE on the line:

SummarySheetRowCounter =
Application.WorksheetFunction.CountBlank(Worksheets(AuditShtName).Range("B2:B65536").Offset(0, AuditTypes * 2 - 2))

So I don't why this happening and I don't know why my tests are showing it
happening in the places its happening in!

Thanks

For Each sh In ActiveWorkbook.Worksheets
For AuditTypes = 1 To ChkbxArraySum
For Each Cell In sh.UsedRange
SummarySheetRowCounter =
Application.WorksheetFunction.CountBlank(Worksheets(AuditShtName).Range("B2:B65536").Offset(0, AuditTypes * 2 - 2))
If SummarySheetRowCounter = 1 Then Exit For
'On Error Resume Next
If MainUserForm.IgnoreBlanksBttn = True And _
IsEmpty(Cell) Then
'do nothing and let loop advance to next
'cell in UsedRange
ElseIf CellIsHidden(Cell) And ProtectedCellsChkBx = False Then
'Do nothing
Else
'Do something
End If
Next
Next
Next

Public Function CellIsHidden(Cell As Range)
If Cell.Parent.Protect = True Then
If Cell.FormulaHidden = True Then
CellIsHidden = True
End If
End If
End Function
 
Protect is a method, not a property. Use

If Cell.Parent.ProtectionMode = True Then

you are setting it to true by that line, and since it is not returning an
error code, it is True, so you are not being told anything.
 
Thanks I think that works!

EM

William Benson said:
Protect is a method, not a property. Use

If Cell.Parent.ProtectionMode = True Then

you are setting it to true by that line, and since it is not returning an
error code, it is True, so you are not being told anything.
Click to expand...
 
Another quick question. When I start my macro, I want to be able to test
each sheet to see if it protected and if so, then try to unprotect it. The
code below works assuming there is no password. However, I want to put in
error handling that says if prompted for a password, then resume. On Error
Resume Next does not seem to work?

Sub UnprotectSheets()
Dim sh As Worksheet
On Error Resume Next
For Each sh In ActiveWorkbook.Worksheets
sh.Unprotect
Next
End Sub
 
Back
Top