Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to protect and unprotect 30 worksheets in a file every month .
Situation: I have a workbook with over 30 worksheets, the worksheets have
data entry areas which are unblocked (our data entry person puts the data in those areas) and also formula areas which are blocked, I protect each worksheet individualy to save the formulas from being altered accidently by data entry person. Every month I need to unprotect all 30 worksheets and adjust the formulas for the new month and then protect all 30 worksheets again to make them ready for data entry person again. Question:Is there any way to protect all 30 worksheets in one step instead of 30 times, and unprotect them all 30 in one step instead of 30 times? IREAD ONLY does not solve the problem, because it does not allow the data entry person to work on the same file. |
#2
|
|||
|
|||
You could use a macro like this, will protect and unprotect all sheets in
the workbook 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 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 "Protect & Unprotect Several Worksheets" <Protect & Unprotect Several Worksheets @discussions.microsoft.com wrote in message ... Situation: I have a workbook with over 30 worksheets, the worksheets have data entry areas which are unblocked (our data entry person puts the data in those areas) and also formula areas which are blocked, I protect each worksheet individualy to save the formulas from being altered accidently by data entry person. Every month I need to unprotect all 30 worksheets and adjust the formulas for the new month and then protect all 30 worksheets again to make them ready for data entry person again. Question:Is there any way to protect all 30 worksheets in one step instead of 30 times, and unprotect them all 30 in one step instead of 30 times? IREAD ONLY does not solve the problem, because it does not allow the data entry person to work on the same file. |
#3
|
|||
|
|||
Paul, thanks for your help, now I have my macros for protect & Unprotect,
but anybody can go and run unprotect macro. I used PRIVATE, but then I can't even see them myself to run them when I need to, is there anyway to put password for use of macro? "Paul B" wrote: You could use a macro like this, will protect and unprotect all sheets in the workbook 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 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 "Protect & Unprotect Several Worksheets" <Protect & Unprotect Several Worksheets @discussions.microsoft.com wrote in message ... Situation: I have a workbook with over 30 worksheets, the worksheets have data entry areas which are unblocked (our data entry person puts the data in those areas) and also formula areas which are blocked, I protect each worksheet individualy to save the formulas from being altered accidently by data entry person. Every month I need to unprotect all 30 worksheets and adjust the formulas for the new month and then protect all 30 worksheets again to make them ready for data entry person again. Question:Is there any way to protect all 30 worksheets in one step instead of 30 times, and unprotect them all 30 in one step instead of 30 times? IREAD ONLY does not solve the problem, because it does not allow the data entry person to work on the same file. |
#4
|
|||
|
|||
You can leave the macros Private.
When you go to ToolsMacroMacros, they won't be seen, but you can just type in the name of the macro and hit Run. That's as good as a password if you protect the VBA Project so users can't access the module code. You could also give the macro(s) a shortcut key combo to run. Hard to find one obscure enough that users wouldn't accidentally stumble on it. Gord Dibben Excel MVP On Fri, 7 Jan 2005 16:23:07 -0800, Protect & Unprotect Several Worksheets rosoft.com wrote: Paul, thanks for your help, now I have my macros for protect & Unprotect, but anybody can go and run unprotect macro. I used PRIVATE, but then I can't even see them myself to run them when I need to, is there anyway to put password for use of macro? "Paul B" wrote: You could use a macro like this, will protect and unprotect all sheets in the workbook 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 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 "Protect & Unprotect Several Worksheets" <Protect & Unprotect Several Worksheets @discussions.microsoft.com wrote in message ... Situation: I have a workbook with over 30 worksheets, the worksheets have data entry areas which are unblocked (our data entry person puts the data in those areas) and also formula areas which are blocked, I protect each worksheet individualy to save the formulas from being altered accidently by data entry person. Every month I need to unprotect all 30 worksheets and adjust the formulas for the new month and then protect all 30 worksheets again to make them ready for data entry person again. Question:Is there any way to protect all 30 worksheets in one step instead of 30 times, and unprotect them all 30 in one step instead of 30 times? IREAD ONLY does not solve the problem, because it does not allow the data entry person to work on the same file. |
#5
|
|||
|
|||
Here is one way, you must lock the VBA project so you can't see the password
in it Sub PassWord_To_Run_Macro() Dim MyStr1 As String, MyStr2 As String With ActiveSheet MyStr2 = ("MyPass") 'Change Password here, it is CASE sensitive MyStr1 = InputBox("Password Is Required To Run this Macro ") If MyStr1 = MyStr2 Then 'your code here Else MsgBox ("Access Denied") End If End With 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 "Protect & Unprotect Several Worksheets" rosoft.com wrote in message ... Paul, thanks for your help, now I have my macros for protect & Unprotect, but anybody can go and run unprotect macro. I used PRIVATE, but then I can't even see them myself to run them when I need to, is there anyway to put password for use of macro? "Paul B" wrote: You could use a macro like this, will protect and unprotect all sheets in the workbook 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 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 "Protect & Unprotect Several Worksheets" <Protect & Unprotect Several Worksheets @discussions.microsoft.com wrote in message ... Situation: I have a workbook with over 30 worksheets, the worksheets have data entry areas which are unblocked (our data entry person puts the data in those areas) and also formula areas which are blocked, I protect each worksheet individualy to save the formulas from being altered accidently by data entry person. Every month I need to unprotect all 30 worksheets and adjust the formulas for the new month and then protect all 30 worksheets again to make them ready for data entry person again. Question:Is there any way to protect all 30 worksheets in one step instead of 30 times, and unprotect them all 30 in one step instead of 30 times? IREAD ONLY does not solve the problem, because it does not allow the data entry person to work on the same file. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there any way that you can protect or unprotect a group of wor. | Excel Discussion (Misc queries) |