Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for protecting and unprotecting multiple worksheets
I would say that I am a moderate user of Excel and I need some help with
running a Macro. I have a workbook that has about 60 pages in it that several people have access to in order to edit information on each of the sheets. Each sheet has a Vlookup table that I am constantly modifying to keep items current. My problem is that I am unlocking worksheets all day long. The password is the same on all of the sheets. Is there a simple macro I can run to unlock the entire workbook rather than individually unlocking each sheet? And the same thing when I am finished and putting the file back. Can I run a macro to reassign the password protection on all of the sheets in the workbook? Thanks! -- -CRM |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for protecting and unprotecting multiple worksheets
Option Explicit
Sub UnprotectAll() dim wks as worksheet for each wks in activeworkbook.worksheets wks.unprotect password:="topsecret" next wks End Sub Sub ProtectAll() dim wks as worksheet for each wks in activeworkbook.worksheets wks.protect password:="topsecret" next wks End Sub saltnsnails wrote: I would say that I am a moderate user of Excel and I need some help with running a Macro. I have a workbook that has about 60 pages in it that several people have access to in order to edit information on each of the sheets. Each sheet has a Vlookup table that I am constantly modifying to keep items current. My problem is that I am unlocking worksheets all day long. The password is the same on all of the sheets. Is there a simple macro I can run to unlock the entire workbook rather than individually unlocking each sheet? And the same thing when I am finished and putting the file back. Can I run a macro to reassign the password protection on all of the sheets in the workbook? Thanks! -- -CRM -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for protecting and unprotecting multiple worksheets
Thank you but I don't know what to do with that? Where do I enter it? How
do I use it? I am still kind of new to this stuff. Thanks! -- -CRM "Dave Peterson" wrote: Option Explicit Sub UnprotectAll() dim wks as worksheet for each wks in activeworkbook.worksheets wks.unprotect password:="topsecret" next wks End Sub Sub ProtectAll() dim wks as worksheet for each wks in activeworkbook.worksheets wks.protect password:="topsecret" next wks End Sub saltnsnails wrote: I would say that I am a moderate user of Excel and I need some help with running a Macro. I have a workbook that has about 60 pages in it that several people have access to in order to edit information on each of the sheets. Each sheet has a Vlookup table that I am constantly modifying to keep items current. My problem is that I am unlocking worksheets all day long. The password is the same on all of the sheets. Is there a simple macro I can run to unlock the entire workbook rather than individually unlocking each sheet? And the same thing when I am finished and putting the file back. Can I run a macro to reassign the password protection on all of the sheets in the workbook? Thanks! -- -CRM -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for protecting and unprotecting multiple worksheets
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. And while you're in the VBE... select your poject Tools|VBAProject Properties|Protection tab Give it a memorable password. It'll make finding the password a little more difficult--keep prying eyes out of your code! saltnsnails wrote: Thank you but I don't know what to do with that? Where do I enter it? How do I use it? I am still kind of new to this stuff. Thanks! -- -CRM "Dave Peterson" wrote: Option Explicit Sub UnprotectAll() dim wks as worksheet for each wks in activeworkbook.worksheets wks.unprotect password:="topsecret" next wks End Sub Sub ProtectAll() dim wks as worksheet for each wks in activeworkbook.worksheets wks.protect password:="topsecret" next wks End Sub saltnsnails wrote: I would say that I am a moderate user of Excel and I need some help with running a Macro. I have a workbook that has about 60 pages in it that several people have access to in order to edit information on each of the sheets. Each sheet has a Vlookup table that I am constantly modifying to keep items current. My problem is that I am unlocking worksheets all day long. The password is the same on all of the sheets. Is there a simple macro I can run to unlock the entire workbook rather than individually unlocking each sheet? And the same thing when I am finished and putting the file back. Can I run a macro to reassign the password protection on all of the sheets in the workbook? Thanks! -- -CRM -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for protecting and unprotecting multiple worksheets
OMG!!!! Thank you!!! AWESOME AWESOME AWESOME!!!! The keystrokes I am going
to save!!!! Thanks! -- -CRM "Dave Peterson" wrote: If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. And while you're in the VBE... select your poject Tools|VBAProject Properties|Protection tab Give it a memorable password. It'll make finding the password a little more difficult--keep prying eyes out of your code! saltnsnails wrote: Thank you but I don't know what to do with that? Where do I enter it? How do I use it? I am still kind of new to this stuff. Thanks! -- -CRM "Dave Peterson" wrote: Option Explicit Sub UnprotectAll() dim wks as worksheet for each wks in activeworkbook.worksheets wks.unprotect password:="topsecret" next wks End Sub Sub ProtectAll() dim wks as worksheet for each wks in activeworkbook.worksheets wks.protect password:="topsecret" next wks End Sub saltnsnails wrote: I would say that I am a moderate user of Excel and I need some help with running a Macro. I have a workbook that has about 60 pages in it that several people have access to in order to edit information on each of the sheets. Each sheet has a Vlookup table that I am constantly modifying to keep items current. My problem is that I am unlocking worksheets all day long. The password is the same on all of the sheets. Is there a simple macro I can run to unlock the entire workbook rather than individually unlocking each sheet? And the same thing when I am finished and putting the file back. Can I run a macro to reassign the password protection on all of the sheets in the workbook? Thanks! -- -CRM -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for protecting and unprotecting multiple worksheets
Dave,
If I wanted to share a workbook that uses this macro to protect/unprotect the sheets, could it be modified to promt me for the password? Otherwise the share-ees could run the macro and still gain access. Thanks for the help, Bruce "Dave Peterson" wrote: Option Explicit Sub UnprotectAll() dim wks as worksheet for each wks in activeworkbook.worksheets wks.unprotect password:="topsecret" next wks End Sub Sub ProtectAll() dim wks as worksheet for each wks in activeworkbook.worksheets wks.protect password:="topsecret" next wks End Sub saltnsnails wrote: I would say that I am a moderate user of Excel and I need some help with running a Macro. I have a workbook that has about 60 pages in it that several people have access to in order to edit information on each of the sheets. Each sheet has a Vlookup table that I am constantly modifying to keep items current. My problem is that I am unlocking worksheets all day long. The password is the same on all of the sheets. Is there a simple macro I can run to unlock the entire workbook rather than individually unlocking each sheet? And the same thing when I am finished and putting the file back. Can I run a macro to reassign the password protection on all of the sheets in the workbook? Thanks! -- -CRM -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for protecting and unprotecting multiple worksheets
Option Explicit
Sub UnprotectAll() Dim wks As Worksheet Pword = InputBox("enter the password") On Error GoTo endit For Each wks In ActiveWorkbook.Worksheets wks.Unprotect Password:=Pword Next wks endit: MsgBox "Incorrect Password" End Sub Correct Pword would be "topsecret" if you used the ProtectAll sub Gord Dibben MS Excel MVP On Thu, 24 Jan 2008 13:45:05 -0800, brumanchu wrote: Dave, If I wanted to share a workbook that uses this macro to protect/unprotect the sheets, could it be modified to promt me for the password? Otherwise the share-ees could run the macro and still gain access. Thanks for the help, Bruce "Dave Peterson" wrote: Option Explicit Sub UnprotectAll() dim wks as worksheet for each wks in activeworkbook.worksheets wks.unprotect password:="topsecret" next wks End Sub Sub ProtectAll() dim wks as worksheet for each wks in activeworkbook.worksheets wks.protect password:="topsecret" next wks End Sub saltnsnails wrote: I would say that I am a moderate user of Excel and I need some help with running a Macro. I have a workbook that has about 60 pages in it that several people have access to in order to edit information on each of the sheets. Each sheet has a Vlookup table that I am constantly modifying to keep items current. My problem is that I am unlocking worksheets all day long. The password is the same on all of the sheets. Is there a simple macro I can run to unlock the entire workbook rather than individually unlocking each sheet? And the same thing when I am finished and putting the file back. Can I run a macro to reassign the password protection on all of the sheets in the workbook? Thanks! -- -CRM -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for protecting and unprotecting multiple worksheets
You may want to put it in a different workbook and not share that workbook with
the macro. Then you can open the macro workbook and open the real workbook And with the real workbook open, you can use: alt-f8 and run either macro. ==== Or... Option Explicit Sub UnprotectAll() Dim wks As Worksheet Dim pwd As String pwd = InputBox(Prompt:="What's the password, Kenny?") If Trim(pwd) = "" Then Exit Sub End If For Each wks In ActiveWorkbook.Worksheets With wks If .ProtectContents = True _ Or .ProtectDrawingObjects = True _ Or .ProtectScenarios = True Then On Error Resume Next .Unprotect Password:=pwd If Err.Number < 0 Then MsgBox "Something went wrong with: " & wks.Name Err.Clear 'exit for 'stop trying??? End If On Error GoTo 0 End If End With Next wks End Sub Sub ProtectAll() Dim wks As Worksheet Dim pwd As String pwd = InputBox(Prompt:="What's the password, Kenny?") If Trim(pwd) = "" Then Exit Sub End If For Each wks In ActiveWorkbook.Worksheets With wks If .ProtectContents = True _ Or .ProtectDrawingObjects = True _ Or .ProtectScenarios = True Then 'do nothing, it's already protected Else On Error Resume Next .Protect Password:=pwd If Err.Number < 0 Then MsgBox "Something went wrong with: " & wks.Name Err.Clear 'exit for 'stop trying??? End If On Error GoTo 0 End If End With Next wks End Sub brumanchu wrote: Dave, If I wanted to share a workbook that uses this macro to protect/unprotect the sheets, could it be modified to promt me for the password? Otherwise the share-ees could run the macro and still gain access. Thanks for the help, Bruce "Dave Peterson" wrote: Option Explicit Sub UnprotectAll() dim wks as worksheet for each wks in activeworkbook.worksheets wks.unprotect password:="topsecret" next wks End Sub Sub ProtectAll() dim wks as worksheet for each wks in activeworkbook.worksheets wks.protect password:="topsecret" next wks End Sub saltnsnails wrote: I would say that I am a moderate user of Excel and I need some help with running a Macro. I have a workbook that has about 60 pages in it that several people have access to in order to edit information on each of the sheets. Each sheet has a Vlookup table that I am constantly modifying to keep items current. My problem is that I am unlocking worksheets all day long. The password is the same on all of the sheets. Is there a simple macro I can run to unlock the entire workbook rather than individually unlocking each sheet? And the same thing when I am finished and putting the file back. Can I run a macro to reassign the password protection on all of the sheets in the workbook? Thanks! -- -CRM -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protecting Multiple Worksheets at the same time | Excel Worksheet Functions | |||
Unprotecting worksheets | Excel Worksheet Functions | |||
Protecting multiple worksheets. | Excel Discussion (Misc queries) | |||
protecting multiple worksheets | Excel Discussion (Misc queries) | |||
PROTECTING/UNPROTECTING SHEETS | Excel Discussion (Misc queries) |