Unprotected sheet is protected?

[salgud]

The following code clears the raw data out of the data entry worksheets in
the workbook. Here is the macro:

Private Sub ClearAllData()
Dim ws As Worksheet
Dim rRangeStart As Range
Dim rRangeErase As Range
Dim lRow As Long
Dim wsSheet1 As Worksheet
Dim sPWORD As String

Application.EnableEvents = False
Set wsSheet1 = ThisWorkbook.Worksheets("Sheet1")

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Totals" Then
wsSheet1.Visible = True

wsSheet1.Range("D2") = ws.Name
sPWORD = wsSheet1.Range("D1")
With ws
.Unprotect Password:=sPWORD
End With
Set rRangeStart = Range("A8")

With ws.Range("A7").CurrentRegion
lRow = .Rows(.Rows.Count).Row
End With

Set rRangeErase = Range(rRangeStart, Cells(lRow, "P"))
rRangeErase.ClearContents <------ THE CELL OR CHART YOU ARE TRYING TO
CHANGE IS PROTECTED AND THEREFORE READ-ONLY
With ws
.Protect Password:=sPWORD
End With
End If
Next ws

wsSheet1.Visible = False
Application.EnableEvents = True
End Sub

The problem is that the "protection" is not XL protection, but created by
an event driven macro. When the user attempts to enter data, s/he is
prompted for the password for that particular sheet. The passwords are
stored in a normally hidden sheet ("Sheet1"). I thought that by turning off
EnableEvents, the sheet protection macro would no longer be triggered and I
could edit these sheets. Why is this not so? Is there an easy fix?

 

[salgud]

The following code clears the raw data out of the data entry worksheets in
the workbook. Here is the macro:

Private Sub ClearAllData()
Dim ws As Worksheet
Dim rRangeStart As Range
Dim rRangeErase As Range
Dim lRow As Long
Dim wsSheet1 As Worksheet
Dim sPWORD As String

Application.EnableEvents = False
Set wsSheet1 = ThisWorkbook.Worksheets("Sheet1")

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Totals" Then
wsSheet1.Visible = True

wsSheet1.Range("D2") = ws.Name
sPWORD = wsSheet1.Range("D1")
With ws
.Unprotect Password:=sPWORD
End With
Set rRangeStart = Range("A8")

With ws.Range("A7").CurrentRegion
lRow = .Rows(.Rows.Count).Row
End With

Set rRangeErase = Range(rRangeStart, Cells(lRow, "P"))
rRangeErase.ClearContents <------ THE CELL OR CHART YOU ARE TRYING TO
CHANGE IS PROTECTED AND THEREFORE READ-ONLY
With ws
.Protect Password:=sPWORD
End With
End If
Next ws

wsSheet1.Visible = False
Application.EnableEvents = True
End Sub

The problem is that the "protection" is not XL protection, but created by
an event driven macro. When the user attempts to enter data, s/he is
prompted for the password for that particular sheet. The passwords are
stored in a normally hidden sheet ("Sheet1"). I thought that by turning off
EnableEvents, the sheet protection macro would no longer be triggered and I
could edit these sheets. Why is this not so? Is there an easy fix?

I should have mentioned that the "regular" sheet protection steps in the
macro have been remarked out now. I put those in before I remembered the
protection scheme for this workbook was different than normal.

 

[salgud]

I should have mentioned that the "regular" sheet protection steps in the
macro have been remarked out now. I put those in before I remembered the
protection scheme for this workbook was different than normal.

Nevermind, got it fixed.