How do I protect a number of sheets allowing the user to add a com

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Prashanth KR - 21 Aug 2007 17:20 GMT

Hi,

How do I protect a workbook having a number of sheets (around 50) allowing
the user to add a comment. I tried this with the following macro:

Sub Protect()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="Protect"
Next ws
End Sub

But it would not allow me to add a "Comment" in the unprotected cells.
Kindly help me to create a macro so that I can protect all sheets at one shot
and also allow users to add comments in the unprotected cells.

Please also note that Iam very new to VBA.

Your help is much appreciated,
Prashanth KR

Reply to this Message

Gary''s Student - 21 Aug 2007 18:42 GMT

For a SINGLE sheet that is unProtected:

Sub Macro1()
With ActiveSheet
.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True
.EnableSelection = xlUnlockedCells
End With
End Sub

will:
1. Protect the sheet
2. allow unlocked cells to receive comments
3. not allow locked cells to receive comments

Insert in a loop

Signature

Gary''s Student - gsnu200738

> Hi,
>
[quoted text clipped - 16 lines]
> Your help is much appreciated,
> Prashanth KR

Reply to this Message

Prashanth KR - 22 Aug 2007 09:56 GMT

Hello Gary,

It was really very helpful. Thanks again. Hope to be in touch with you.

My appreciations for the immediate response.

Prashanth KR.

> For a SINGLE sheet that is unProtected:
>
[quoted text clipped - 32 lines]
> > Your help is much appreciated,
> > Prashanth KR

Reply to this Message

Gord Dibben - 21 Aug 2007 18:54 GMT

Sub Protect()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws
.Protect Password:="Protect", DrawingObjects:=False, Contents:=True
.EnableSelection = xlUnlockedCells
End With
Next ws
End Sub

Gord Dibben MS Excel MVP

>Hi,
>
[quoted text clipped - 16 lines]
>Your help is much appreciated,
>Prashanth KR

Reply to this Message

Prashanth KR - 22 Aug 2007 10:02 GMT

Wow.... this worked wonderful. And this is what I was looking at. Thanks a
lot Gord. Iam totally impressed and saved me a lot of time as I had more than
50 sheets.

My appreciations for your immediate response.
Prashanth KR.

> Sub Protect()
> Dim ws As Worksheet
[quoted text clipped - 28 lines]
> >Your help is much appreciated,
> >Prashanth KR

Reply to this Message

Gord Dibben - 22 Aug 2007 15:16 GMT

You're welcome.............both times<g>

Gord

>Wow.... this worked wonderful. And this is what I was looking at. Thanks a
>lot Gord. Iam totally impressed and saved me a lot of time as I had more than
[quoted text clipped - 35 lines]
>> >Your help is much appreciated,
>> >Prashanth KR

Reply to this Message

Prashanth KR - 28 Aug 2007 12:40 GMT

Hi Gord,

Iam encountering another problem. I tried, to unprotect the sheets with the
following Macro to make some modifications:

Sub UnProtect()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws
.UnProtect Password:="Protect"
.EnableSelection = xlUnlockedCells
End With
Next ws
End Sub

But, when I try again, to protect the sheets with your macro, it does
protects all sheets, but it does not allow me to "Select the Locked Cells".
Kindly help.

Thanks in advance,
Prashanth KR.

> You're welcome.............both times<g>
>
[quoted text clipped - 39 lines]
> >> >Your help is much appreciated,
> >> >Prashanth KR

Reply to this Message

Gord Dibben - 28 Aug 2007 16:06 GMT

>> but it does not allow me to "Select the Locked Cells".

The code does its job.

Not allowing you to select locked cells, only unlocked cells.

If you want to select locked and unlocked cells remove the line

.EnableSelection = xlUnlockedCells

Gord

>Hi Gord,
>
[quoted text clipped - 61 lines]
>> >> >Your help is much appreciated,
>> >> >Prashanth KR

Reply to this Message

Prashanth KR - 22 Aug 2007 10:04 GMT

Wow Gord .... this is what I was looking for. It worked perfectly.

Thanks again,

My sincere appreciations for your immediate response.
Prashanth KR.

> Sub Protect()
> Dim ws As Worksheet
[quoted text clipped - 28 lines]
> >Your help is much appreciated,
> >Prashanth KR

Reply to this Message