Hello - I have created a workbook with 30 sheets; each sheet has been
protected so that my co-workers are not able to alter certain cells. Upon
opening this documents, however, they receive a warning message telling them
that they are unable to alter some of the cells because protection is in
place. This message seems to appear once for every sheet; i.e. they have to
click Okay 30 times prior to proceeding. Is there any way of getting rid of
this warning message? It is unnecessary and will cause panic, confusion, and
delay.

Any help on this matter would be wonderful! Perhaps is there a macro that
can be entered to click the Okay 30 times for them?

Thank you!

Hi,

I don't understand what this message is, you shouldn't recieve a message
unless you actually try and change a protected cell. Try opening the workbook
with macros disabled, do you still get the message?

Mike

"NFaye" wrote:

Hello - I have created a workbook with 30 sheets; each sheet has been
protected so that my co-workers are not able to alter certain cells. Upon
opening this documents, however, they receive a warning message telling them
that they are unable to alter some of the cells because protection is in
place. This message seems to appear once for every sheet; i.e. they have to
click Okay 30 times prior to proceeding. Is there any way of getting rid of
this warning message? It is unnecessary and will cause panic, confusion, and
delay.

Any help on this matter would be wonderful! Perhaps is there a macro that
can be entered to click the Okay 30 times for them?

Thank you!

Hi Mike - I still get the following message, despite disabling the macros:

"The cell or chart you are trying to change is protected and therefore
read-only. To modify a protected cell or chart, first remove protection
using the unprotect Sheet Command."

"Mike H" wrote:

Hi,

I don't understand what this message is, you shouldn't recieve a message
unless you actually try and change a protected cell. Try opening the workbook
with macros disabled, do you still get the message?

Mike

"NFaye" wrote:

Hello - I have created a workbook with 30 sheets; each sheet has been
protected so that my co-workers are not able to alter certain cells. Upon
opening this documents, however, they receive a warning message telling them
that they are unable to alter some of the cells because protection is in
place. This message seems to appear once for every sheet; i.e. they have to
click Okay 30 times prior to proceeding. Is there any way of getting rid of
this warning message? It is unnecessary and will cause panic, confusion, and
delay.

Any help on this matter would be wonderful! Perhaps is there a macro that
can be entered to click the Okay 30 times for them?

Thank you!

The first problem can only come from event code that runs on each sheet when the
workbook opens..

Check Thisworkbook for code and edit or delete.

The second problem.................

That is customary and cannot be disabled.

When protecting the sheet using ToolsProtectionProtect Sheet there are options
under "allow users to".

Uncheck "select locked cells" and check "select unlocked cells"

To do all sheets at once you could use a macro.

First use this macro to unprotect all sheets.

Sub UnprotectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Unprotect Password:="justme"
'use this if each sheet has the same password, otherwise Sheets(n) UnProtect
Next n
Application.ScreenUpdating = True
End Sub

Now use this macro to protect all sheets with select locked cells checked.

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Protect Password:="justme"
ActiveSheet.EnableSelection = xlUnlockedCells
Next n
Application.ScreenUpdating = True
End Sub

Both these macros would be placed into a general module.


Gord Dibben MS Excel MVP

On Tue, 19 Feb 2008 10:25:01 -0800, NFaye
wrote:

Hi Mike - I still get the following message, despite disabling the macros:

"The cell or chart you are trying to change is protected and therefore
read-only. To modify a protected cell or chart, first remove protection
using the unprotect Sheet Command."

"Mike H" wrote:

Hi,

I don't understand what this message is, you shouldn't recieve a message
unless you actually try and change a protected cell. Try opening the workbook
with macros disabled, do you still get the message?

Mike

"NFaye" wrote:

Hello - I have created a workbook with 30 sheets; each sheet has been
protected so that my co-workers are not able to alter certain cells. Upon
opening this documents, however, they receive a warning message telling them
that they are unable to alter some of the cells because protection is in
place. This message seems to appear once for every sheet; i.e. they have to
click Okay 30 times prior to proceeding. Is there any way of getting rid of
this warning message? It is unnecessary and will cause panic, confusion, and
delay.

Any help on this matter would be wonderful! Perhaps is there a macro that
can be entered to click the Okay 30 times for them?

Thank you!

Well I don't know about you all but this is how I got rid of that annoying pop up stating that
"The Cell or Chart you are trying to change is protected and therefore Read Only".

First, I only allow any & all edits, changes and entries to each of my 36 sheets through UserForm(s) and I use this code to allow the UserForm to edit in the protected Cells &/or Worksheets:

Private Sub Workbook_Open()
Application.DisplayAlerts = False
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Protect Password:="Secret", _
UserInterFaceOnly:=True
Next wSheet
End Sub

I also do not allow right-click or Double click -- to Notify the people that the cells are protected I use these codes:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
MsgBox "This Workbook has been Secured!" & (Chr(13)) & (Chr(13)) & "Please contact ????? for any questions!", vbOKOnly, "******ATTENTION******"
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
MsgBox "This Workbook has been Secured!" & (Chr(13)) & (Chr(13)) & "Please contact ????? for any questions!", vbOKOnly,"******ATTENTION******"
End Sub

Now, with just this if you Double-Click then you will receive my Warning Box and after "OK" you will get the dreaded --
"The Cell or Chart you are trying to change is protected and therefore Read Only".

Now go into:
Tools Options Edit
Deselect Edit directly in Cell.
Your DONE!!!!