Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
some 'Protection' questions
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 ToolsProtectionProtect 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
some 'Protection' 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 ToolsProtectionProtect 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 "Bri" wrote in message ... 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 ToolsProtectionProtect 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
some 'Protection' questions
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 "Paul B" wrote in message ... 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 ToolsProtectionProtect 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 "Bri" wrote in message ... 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 ToolsProtectionProtect 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
some 'Protection' questions
Quite helpful Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
some 'Protection' questions
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 "Bri" wrote in message ... Quite helpful Thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
some 'Protection' questions
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 On Tue, 24 Jan 2006 16:49:17 -0500, "Bri" wrote: 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 ToolsProtectionProtect 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code error with Protection turned on - help please | Excel Discussion (Misc queries) | |||
Illinois Personal Information Protection Act | Excel Worksheet Functions | |||
Pivot Table for survey data w/ questions as Rows & poss answrs as | Excel Discussion (Misc queries) | |||
workbook protection | New Users to Excel | |||
protection on excel forms | Excel Worksheet Functions |