| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to protect multiple sheets?
Hello! Thanks in advance for your help.
How can I modify the macro (below line) I recorded to protect a sheet so that I can use it multiple sheets at once. I have a workbook with 20 tabs. I'd like to be able to protect and unprotect all sheets at once. _________________________ PROTECTSHEET Macro Keyboard Shortcut: Ctrl+p ActiveSheet.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True __________________________ |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro to protect multiple sheets?
Dim ws as worksheet
for each ws in thisworkbook.worksheets ws.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True next ws "Stilla" wrote: Hello! Thanks in advance for your help. How can I modify the macro (below line) I recorded to protect a sheet so that I can use it multiple sheets at once. I have a workbook with 20 tabs. I'd like to be able to protect and unprotect all sheets at once. _________________________ PROTECTSHEET Macro Keyboard Shortcut: Ctrl+p ActiveSheet.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True __________________________ |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro to protect multiple sheets?
Duke - Fantastic! it WORKS!! One small glitch...
When I protect, I'm still able to select locked cells... how do I modify this so that the user is only able to select unlocked cells on a protected sheet? Thanks again. "Duke Carey" wrote: Dim ws as worksheet for each ws in thisworkbook.worksheets ws.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True next ws "Stilla" wrote: Hello! Thanks in advance for your help. How can I modify the macro (below line) I recorded to protect a sheet so that I can use it multiple sheets at once. I have a workbook with 20 tabs. I'd like to be able to protect and unprotect all sheets at once. _________________________ PROTECTSHEET Macro Keyboard Shortcut: Ctrl+p ActiveSheet.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True __________________________ |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro to protect multiple sheets?
try
Dim ws as worksheet for each ws in thisworkbook.worksheets ws.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True ws.EnableSelection = xlUnlockedCells next ws "Stilla" wrote: Duke - Fantastic! it WORKS!! One small glitch... When I protect, I'm still able to select locked cells... how do I modify this so that the user is only able to select unlocked cells on a protected sheet? Thanks again. "Duke Carey" wrote: Dim ws as worksheet for each ws in thisworkbook.worksheets ws.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True next ws "Stilla" wrote: Hello! Thanks in advance for your help. How can I modify the macro (below line) I recorded to protect a sheet so that I can use it multiple sheets at once. I have a workbook with 20 tabs. I'd like to be able to protect and unprotect all sheets at once. _________________________ PROTECTSHEET Macro Keyboard Shortcut: Ctrl+p ActiveSheet.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True __________________________ |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro to protect multiple sheets?
Never mind. I found my error. Sorry to bother. Thanks for the help.
Russ "Duke Carey" wrote: try Dim ws as worksheet for each ws in thisworkbook.worksheets ws.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True ws.EnableSelection = xlUnlockedCells next ws "Stilla" wrote: Duke - Fantastic! it WORKS!! One small glitch... When I protect, I'm still able to select locked cells... how do I modify this so that the user is only able to select unlocked cells on a protected sheet? Thanks again. "Duke Carey" wrote: Dim ws as worksheet for each ws in thisworkbook.worksheets ws.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True next ws "Stilla" wrote: Hello! Thanks in advance for your help. How can I modify the macro (below line) I recorded to protect a sheet so that I can use it multiple sheets at once. I have a workbook with 20 tabs. I'd like to be able to protect and unprotect all sheets at once. _________________________ PROTECTSHEET Macro Keyboard Shortcut: Ctrl+p ActiveSheet.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True __________________________ |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro to protect multiple sheets?
OH HOW VERY VERY COOOOL! Thanks Duke! You have already saved me a
significant amount of time!! I adjusted slightly to now work on active workbooks(For Each ws In ActiveWorkbook.Worksheets) Question 1) How could I refine this further to apply to only the selected worksheets in the active workbook? Question 2) My UN-protecting Macro (For Each ws In ActiveWorkbook.Worksheets ws.UNPROTECT) works but it requires me to enter the password as it moves from tab to tab. How can I modify so that I can unprotect ALL selected worksheets with only ONE entry of the password? THANKS SO MUCH - I'm starting to become very fond of Macros! Stilla _____________ "Duke Carey" wrote: try Dim ws as worksheet for each ws in thisworkbook.worksheets ws.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True ws.EnableSelection = xlUnlockedCells next ws "Stilla" wrote: Duke - Fantastic! it WORKS!! One small glitch... When I protect, I'm still able to select locked cells... how do I modify this so that the user is only able to select unlocked cells on a protected sheet? Thanks again. "Duke Carey" wrote: Dim ws as worksheet for each ws in thisworkbook.worksheets ws.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True next ws "Stilla" wrote: Hello! Thanks in advance for your help. How can I modify the macro (below line) I recorded to protect a sheet so that I can use it multiple sheets at once. I have a workbook with 20 tabs. I'd like to be able to protect and unprotect all sheets at once. _________________________ PROTECTSHEET Macro Keyboard Shortcut: Ctrl+p ActiveSheet.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True __________________________ |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro to protect multiple sheets?
Stilla
1. Use this code Sub Protect_Selected_Sheets() Set MySheets = ActiveWindow.SelectedSheets For Each WS In MySheets WS.Select WS.Protect Password:="justme" Next WS End Sub 2. The sheets have to first be protected with the same password for each. Sub ProtectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Protect Password:="justme" Next n Application.ScreenUpdating = True End Sub Gord Dibben MS Excel MVP On Wed, 28 Feb 2007 08:18:39 -0800, Stilla wrote: OH HOW VERY VERY COOOOL! Thanks Duke! You have already saved me a significant amount of time!! I adjusted slightly to now work on active workbooks(For Each ws In ActiveWorkbook.Worksheets) Question 1) How could I refine this further to apply to only the selected worksheets in the active workbook? Question 2) My UN-protecting Macro (For Each ws In ActiveWorkbook.Worksheets ws.UNPROTECT) works but it requires me to enter the password as it moves from tab to tab. How can I modify so that I can unprotect ALL selected worksheets with only ONE entry of the password? THANKS SO MUCH - I'm starting to become very fond of Macros! Stilla _____________ "Duke Carey" wrote: try Dim ws as worksheet for each ws in thisworkbook.worksheets ws.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True ws.EnableSelection = xlUnlockedCells next ws "Stilla" wrote: Duke - Fantastic! it WORKS!! One small glitch... When I protect, I'm still able to select locked cells... how do I modify this so that the user is only able to select unlocked cells on a protected sheet? Thanks again. "Duke Carey" wrote: Dim ws as worksheet for each ws in thisworkbook.worksheets ws.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True next ws "Stilla" wrote: Hello! Thanks in advance for your help. How can I modify the macro (below line) I recorded to protect a sheet so that I can use it multiple sheets at once. I have a workbook with 20 tabs. I'd like to be able to protect and unprotect all sheets at once. _________________________ PROTECTSHEET Macro Keyboard Shortcut: Ctrl+p ActiveSheet.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True __________________________ |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro to protect multiple sheets?
Hello Duke!
Regarding the LAST line: "ws.EnableSelection = xlUnlockedCells" This works great as long as the workbook remains open. HOWEVER, if I close it and open it again, then I found that I can still select the locked cells (don't want this). Do you have another piece of magic code to make it right? THANKS SO MUCH! "Duke Carey" wrote: try Dim ws as worksheet for each ws in thisworkbook.worksheets ws.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True ws.EnableSelection = xlUnlockedCells next ws "Stilla" wrote: Duke - Fantastic! it WORKS!! One small glitch... When I protect, I'm still able to select locked cells... how do I modify this so that the user is only able to select unlocked cells on a protected sheet? Thanks again. "Duke Carey" wrote: Dim ws as worksheet for each ws in thisworkbook.worksheets ws.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True next ws "Stilla" wrote: Hello! Thanks in advance for your help. How can I modify the macro (below line) I recorded to protect a sheet so that I can use it multiple sheets at once. I have a workbook with 20 tabs. I'd like to be able to protect and unprotect all sheets at once. _________________________ PROTECTSHEET Macro Keyboard Shortcut: Ctrl+p ActiveSheet.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True __________________________ |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro to protect multiple sheets?
Put the procedure in a general module and call the procedure Auto_Open.
Excel time you open the file, auto_open() will run (if you allow macros to run). Stilla wrote: Hello Duke! Regarding the LAST line: "ws.EnableSelection = xlUnlockedCells" This works great as long as the workbook remains open. HOWEVER, if I close it and open it again, then I found that I can still select the locked cells (don't want this). Do you have another piece of magic code to make it right? THANKS SO MUCH! "Duke Carey" wrote: try Dim ws as worksheet for each ws in thisworkbook.worksheets ws.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True ws.EnableSelection = xlUnlockedCells next ws "Stilla" wrote: Duke - Fantastic! it WORKS!! One small glitch... When I protect, I'm still able to select locked cells... how do I modify this so that the user is only able to select unlocked cells on a protected sheet? Thanks again. "Duke Carey" wrote: Dim ws as worksheet for each ws in thisworkbook.worksheets ws.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True next ws "Stilla" wrote: Hello! Thanks in advance for your help. How can I modify the macro (below line) I recorded to protect a sheet so that I can use it multiple sheets at once. I have a workbook with 20 tabs. I'd like to be able to protect and unprotect all sheets at once. _________________________ PROTECTSHEET Macro Keyboard Shortcut: Ctrl+p ActiveSheet.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True __________________________ -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro to protect multiple sheets?
Duke,
Pardon my jump in, but I have the exact same need. I tried your solution but it seems only to work on the first worksheet. Any idea why that may be? Thanks - Russ Sub protectit() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ActiveSheet.Protect Password:="myPW", DrawingObjects:=True, Contents:=True, Scenarios:=True Next ws End Sub "Duke Carey" wrote: Dim ws as worksheet for each ws in thisworkbook.worksheets ws.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True next ws "Stilla" wrote: Hello! Thanks in advance for your help. How can I modify the macro (below line) I recorded to protect a sheet so that I can use it multiple sheets at once. I have a workbook with 20 tabs. I'd like to be able to protect and unprotect all sheets at once. _________________________ PROTECTSHEET Macro Keyboard Shortcut: Ctrl+p ActiveSheet.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True __________________________ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Use Macro to protect choosen sheets in workbook | Excel Discussion (Misc queries) | |||
| macro to compile columns on multiple sheets | Excel Discussion (Misc queries) | |||
| creating macro working across multiple sheets | Excel Worksheet Functions | |||
| How do I protect multiple sheets at one time? | Excel Worksheet Functions | |||
| How do I protect formula cells on multiple sheets? | Excel Worksheet Functions | |||
Hybrid Mode
