Accidentally Protecting Sheets

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.


"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
>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

Thanks I think that works!

EM

"William Benson" wrote:

> 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.
>
>
> "ExcelMonkey" <[email protected]> wrote in message
> news:[email protected]...
> >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
>
>
>

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

"William Benson" wrote:

> 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.
>
>
> "ExcelMonkey" <[email protected]> wrote in message
> news:[email protected]...
> >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
>
>
>

You were too quick to thank me, and I was too quick to give my response:

Read this, there are several properties to check BESIDES ProtectionMode.

Make sure to read where Bob Ogilb\vy jumps in...

http://www.excelforum.com/archive/in.../t-210941.html

"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "William Benson" wrote:
>
>> 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.
>>
>>
>> "ExcelMonkey" <[email protected]> wrote in message
>> news:[email protected]...
>> >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
>>
>>
>>