Protect Workbook

[J.T.]

Paul B, gave me the codes below which works great,
however, I would like to be able to insert comments, and
rows/columns into the protected sheets, how do I amend
the code to do that?

Sub protect_sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect
Next ws
End Sub

Sub unprotect_sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect
Next ws
End Sub

Or with password

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

Sub unprotect_sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect password:="123"
Next ws
End Sub

 

[Jim Rech]

Since you cannot add comments to cells when worksheet protection is on you'd
have to unprotect the sheet first.

--
Jim Rech
Excel MVP

| Paul B, gave me the codes below which works great,
| however, I would like to be able to insert comments, and
| rows/columns into the protected sheets, how do I amend
| the code to do that?
|
| Sub protect_sheets()
| Dim ws As Worksheet
| For Each ws In ThisWorkbook.Worksheets
| ws.Protect
| Next ws
| End Sub
|
| Sub unprotect_sheets()
| Dim ws As Worksheet
| For Each ws In ThisWorkbook.Worksheets
| ws.Unprotect
| Next ws
| End Sub
|
| Or with password
|
| Sub protect_sheets()
| Dim ws As Worksheet
| For Each ws In ThisWorkbook.Worksheets
| ws.Protect password:="123"
| Next ws
| End Sub
|
| Sub unprotect_sheets()
| Dim ws As Worksheet
| For Each ws In ThisWorkbook.Worksheets
| ws.Unprotect password:="123"
| Next ws
| End Sub
|
| --
| Paul B
|

 

[Frank Kabel]

Hi
try repalcing the line
ws.protect

with
ws.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
True, AllowInsertingColumns:=True, AllowInsertingRows:=True

 

[Guest]

JT
you can do this outside of code
highlight the rows or columns you would like to have access to for inserting
go to Format, Cells, Protection and uncheck the "Locked" bo

----- J.T. wrote: ----

Paul B, gave me the codes below which works great,
however, I would like to be able to insert comments, and
rows/columns into the protected sheets, how do I amend
the code to do that

Sub protect_sheets(
Dim ws As Workshee
For Each ws In ThisWorkbook.Worksheet
ws.Protec
Next w
End Su

Sub unprotect_sheets(
Dim ws As Workshee
For Each ws In ThisWorkbook.Worksheet
ws.Unprotec
Next w
End Su

Or with passwor

Sub protect_sheets(
Dim ws As Workshee
For Each ws In ThisWorkbook.Worksheet
ws.Protect password:="123
Next w
End Su

Sub unprotect_sheets(
Dim ws As Workshee
For Each ws In ThisWorkbook.Worksheet
ws.Unprotect password:="123
Next w
End Su

 

[Frank Kabel]

Hi Jim
using Excel 2003 this is possible

 

[E Edgington]

"AllowInsertingColumns:=True, AllowInsertingRows:=True"

Is this available with Excel 2000, VB 6.0? It doesn't
appear so, unless I'm doing something wrong (entirely
possible!). VB is happy with the code until I add these
criterion. "Compile error: Named argument not found".

 

[Frank Kabel]

Hi
not quite sure but coul be added with Excel XP

 

[Dave Peterson]

Yep.

(Just confirming)

Hi
not quite sure but coul be added with Excel XP