Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protecting Sheet vs. Workbook
I have 12 sheets in a workbook. I've locked the same cells in each sheet.
Once protected I enjoy the luxury of tabing only to the unlocked cells. I've protected the entire workbook, however this feature doesn't work unless I protect each sheet. Is there a way to protect all the sheets at the same time? Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protecting Sheet vs. Workbook
Skydiver, you can with a macro,
Sub Protect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect password:="123" Next ws End Sub -- 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 "Skydiver" wrote in message ... I have 12 sheets in a workbook. I've locked the same cells in each sheet. Once protected I enjoy the luxury of tabing only to the unlocked cells. I've protected the entire workbook, however this feature doesn't work unless I protect each sheet. Is there a way to protect all the sheets at the same time? Thank you. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protecting Sheet vs. Workbook
Paul, I'm sorry. I've never worked with macros before. I'm unfamiliar with
this process. "Paul B" wrote: Skydiver, you can with a macro, Sub Protect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect password:="123" Next ws End Sub -- 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 "Skydiver" wrote in message ... I have 12 sheets in a workbook. I've locked the same cells in each sheet. Once protected I enjoy the luxury of tabing only to the unlocked cells. I've protected the entire workbook, however this feature doesn't work unless I protect each sheet. Is there a way to protect all the sheets at the same time? Thank you. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protecting Sheet vs. Workbook
If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... First...create a backup copy of your original workbook. To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + R to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. Run the macro by going to ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. NOTE: you will probably wish to unprotect all sheets at some point. Copy Paul's macro then change to................. Sub UnProtect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Unprotect Password:="123" Next ws End Sub And for just selected sheets........... Sub Protect_Selected_Sheets() Set MySheets = ActiveWindow.SelectedSheets For Each ws In MySheets ws.Select ws.Protect Password:="123" Next ws End Sub Gord Dibben MS Excel MVP On Sun, 29 Oct 2006 08:42:02 -0800, Skydiver wrote: Paul, I'm sorry. I've never worked with macros before. I'm unfamiliar with this process. "Paul B" wrote: Skydiver, you can with a macro, Sub Protect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect password:="123" Next ws End Sub -- 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 "Skydiver" wrote in message ... I have 12 sheets in a workbook. I've locked the same cells in each sheet. Once protected I enjoy the luxury of tabing only to the unlocked cells. I've protected the entire workbook, however this feature doesn't work unless I protect each sheet. Is there a way to protect all the sheets at the same time? Thank you. Gord Dibben MS Excel MVP |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protecting Sheet vs. Workbook
Thanks Gord.
"Gord Dibben" wrote: If not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... First...create a backup copy of your original workbook. To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + R to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. Run the macro by going to ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. NOTE: you will probably wish to unprotect all sheets at some point. Copy Paul's macro then change to................. Sub UnProtect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Unprotect Password:="123" Next ws End Sub And for just selected sheets........... Sub Protect_Selected_Sheets() Set MySheets = ActiveWindow.SelectedSheets For Each ws In MySheets ws.Select ws.Protect Password:="123" Next ws End Sub Gord Dibben MS Excel MVP On Sun, 29 Oct 2006 08:42:02 -0800, Skydiver wrote: Paul, I'm sorry. I've never worked with macros before. I'm unfamiliar with this process. "Paul B" wrote: Skydiver, you can with a macro, Sub Protect_All_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect password:="123" Next ws End Sub -- 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 "Skydiver" wrote in message ... I have 12 sheets in a workbook. I've locked the same cells in each sheet. Once protected I enjoy the luxury of tabing only to the unlocked cells. I've protected the entire workbook, however this feature doesn't work unless I protect each sheet. Is there a way to protect all the sheets at the same time? Thank you. Gord Dibben MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
One workbook to another . . . | Excel Discussion (Misc queries) | |||
CREATE NEW WORKBOOK AND SHEETS BASED ON COLUMN DATA | Excel Worksheet Functions | |||
Change sheet reference in new workbook | Excel Discussion (Misc queries) | |||
Protect Workbook vs Worksheet?? | Excel Worksheet Functions | |||
Does excel recognise names rather than cells? | Excel Worksheet Functions |