Excel Column or Cell Protection

[Reg]

I can see how to protect a sheet but I cannot figure out
how to protect 2 or 3 columns within a spreadsheet. Can
anyone help?

 

[Chip Pearson]

By default, all cells are locked so that when you protect a
sheet, all cells are protected. To protect only certain cells,
first select all the cells, then go to the Format menu, choose
Cell, then the Protection tab, and uncheck the Locked box. This
will unlock all cells. Next, select the cells you wish to
protect. Go back to the Protection tab and check the Locked
setting. Finally, protect the sheet.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

-----Original Message-----
By default, all cells are locked so that when you protect a
sheet, all cells are protected. To protect only certain cells,
first select all the cells, then go to the Format menu, choose
Cell, then the Protection tab, and uncheck the Locked box. This
will unlock all cells. Next, select the cells you wish to
protect. Go back to the Protection tab and check the Locked
setting. Finally, protect the sheet.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





.
Brilliant. Many thanks.

Reg

 

[Reg]

Chip,

Got the protection bit OK but now I want to copy the
protected sheet to a number of tabs. When I do that the
protection disappears. Do I have to protect each tab
individually?

Reg

 

[Arvi Laanemets]

Hi

How did you copy? When you use Move or Copy feature from tab's right-click
dropdown menu, the copy remains protected.

Arvi Laanemets

 

[Ken Wright]

If you are going to regularly protect and unprotect multiple sheets then you
really want to use code else you have to do it manually:-


Public Sub ProtectAllSheets()
'Space allowed for insertion of a password
'Code lists every sheet with it's protection status
Application.ScreenUpdating = False
Const PWORD As String = ""
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
wkSht.Protect Password:=PWORD
statStr = statStr & ": Protected"
End With
Next wkSht
MsgBox Mid(statStr, 2)
Application.ScreenUpdating = True
End Sub


Public Sub UnprotectAllSheets()
'Space allowed for insertion of a password
'Code lists every sheet with it's protection status
Application.ScreenUpdating = False
Const PWORD As String = ""
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
End With
Next wkSht
MsgBox Mid(statStr, 2)
Application.ScreenUpdating = True
End Sub

 

[Stuart H]

-----Original Message-----
I can see how to protect a sheet but I cannot figure out
how to protect 2 or 3 columns within a spreadsheet. Can
anyone help?
.
You need to highlight the columns (or rows or individual

cells) that you do not want to protect and go to FORMAT >
CELLS and choose the protection tab. Remove the tick from
the 'Locked'.

now go to TOOLS > PROTECTION > PROTECT SHEET. You can
either password protect or just press OK. Everything is
now protected, except the columns/rows/cells that you
chose not to protect.

I trust that this helps