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(Worksheet s(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(Worksheet s(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

Accidentally Protecting Sheets
 

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" wrote in message
...
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(Worksheet s(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(Worksheet s(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

Accidentally Protecting Sheets
 

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" wrote in message
...
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(Worksheet s(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(Worksheet s(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

Accidentally Protecting Sheets
 

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" wrote in message
...
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(Worksheet s(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(Worksheet s(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

Accidentally Protecting Sheets
 

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" wrote in message
...
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" wrote in message
...
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(Worksheet s(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(Worksheet s(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