some 'Protection' questions

[Bri]

Hello

I have a workbook nearing completion and I'm now adding (well, attempting)
protection. I have some questions:

a) Each worksheet has regions of unlocked cells, the others being locked.
I can protect each work sheet one-by-one. Is there a way to protect them
all at once? (Selecting all tabs leaves Tools>Protection>Protect Sheet ...
grayed out.)

b) My worksheets contain macros that either hide or show columns using Subs
with code fragments like 'Columns(N).Hidden = False'. When a worksheet is
protected with the default 'select locked cells' and 'select unlocked
cells' checked, I can't run the macros. If I also check the 'format
columns' protection option, the macros work properly, but the user can alter
column widths (undesirable). Is there an easy solution?

c) I have many worksheets with several regions either locked or unlocked.
Is there an easy way to show the locked status of each cell without having
to check each one individually?

d) I think this one is really dumb, but here goes. I took a test excel
file and fully protected each sheet and the entire workbook (both structure
and windows). As expected, I couldn't really do anything after that, but I
could go into explorer and erase the entire file. Is this easily
preventable?

Much thanks
Bri

 

[Paul B]

a) Each worksheet has regions of unlocked cells, the others being locked.

I can protect each work sheet one-by-one. Is there a way to protect them
all at once? (Selecting all tabs leaves Tools>Protection>Protect Sheet ....
grayed out.)

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

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

b) My worksheets contain macros that either hide or show columns using
Subs

with code fragments like 'Columns(N).Hidden = False'. When a worksheet is
protected with the default 'select locked cells' and 'select unlocked
cells' checked, I can't run the macros. If I also check the 'format
columns' protection option, the macros work properly, but the user can alter
column widths (undesirable). Is there an easy solution?

you can protect the sheet with user interface only, like this, then your
macro will run

ActiveSheet.Protect UserInterfaceOnly:=True, password:="123"

Sheets("Sheet2").Protect UserInterfaceOnly:=True, password:="123"

c) I have many worksheets with several regions either locked or unlocked.
Is there an easy way to show the locked status of each cell without having
to check each one individually?

you could use a macro like this to highlight the unlocked cells

Sub Highlight_Unlocked_Cells()
'will color all unlocked cells in the sheet
'will only remove color index 46 burnt orange
Dim Cel As Range
For Each Cel In ActiveSheet.UsedRange.Cells
If Cel.Interior.ColorIndex = 46 Then Cel.Interior.ColorIndex = 0
Next
For Each Cel In ActiveSheet.UsedRange.Cells
If Cel.Locked = False Then Cel.Interior.ColorIndex = 46
Next
Exit Sub
End Sub

and this to remove the highlight, will remove and cell highlight with that
color

Sub Remove_Highlight_Unlocked_Cells()
'will only remove color index 46 burnt orange
For Each Cel In ActiveSheet.UsedRange.Cells
If Cel.Interior.ColorIndex = 46 Then Cel.Interior.ColorIndex = 0
Next
Exit Sub
End Sub

d) I think this one is really dumb, but here goes. I took a test excel
file and fully protected each sheet and the entire workbook (both structure
and windows). As expected, I couldn't really do anything after that, but I
could go into explorer and erase the entire file. Is this easily
preventable?

I don't think so, also be aware that passwords in excel can be broken very
easily, just do a surch on google and you will find a way to do it


Hope this will help


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Paul B]

And if you want to password protect the sheets chnage ws.Protect to this

ws.Protect password:="123"

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Bri]

Quite helpful
Thanks!

 

[Paul B]

Your welcome

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Gord Dibben]

Bri

Paul has addressed a)

b) unprotect the sheet, run your code then re-protect.

Sub SHEETUNPROTECT()
ActiveSheet.Unprotect Password:="justme"

'your code goes here

ActiveSheet.Protect Password:="justme"
End Sub

c)To highlight all locked cells....................

Sub Locked_Cells()
Dim cell As Range, tempR As Range, rangeToCheck As Range
'check each cell in the selection
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
If cell.Locked Then
If tempR Is Nothing Then
'initialize tempR with the first qualifying cell
Set tempR = cell
Else
'add additional cells to tempR
Set tempR = Union(tempR, cell)
End If
End If
Next cell
'display message and stop if no cells found
If tempR Is Nothing Then
MsgBox "There are no Locked cells " & _
"in the selected range."
End
End If
'select qualifying cells
tempR.Interior.ColorIndex = 3
End Sub

d) No way to prevent deletion of a file unless you have exclusive permissions
for the folder in which the file is stored.

See Windows Help and Support for "permissions". Be very careful with
this.........you could lock yourselef out.


Gord Dibben MS Excel MVP