Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet Protection
is there a way I can protect and unprotect more than one sheet at a
time. I have 40 sheets in a workbook and would like to protect/unprotect 16 at one time. I know I can do it one at a time but I find this time consuming. Thank you, Wally |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet Protection
Hi Wally,
The following macros worked for me... To protect some sheets first use Shift-Click Tab to group those sheets then run the following macro... Public Sub ProtectSelectedSheets() Dim Sht As Worksheet Dim ncProtect As New Collection For Each Sht In ActiveWindow.SelectedSheets ncProtect.Add Item:=Sht Next Sht Worksheets(1).Select For Each Sht In ncProtect Sht.Protect Next Sht End Sub To Unprotect some protected sheets first use Shift-Click Tab to group those sheets then run the following macro... Public Sub UnprotectSelectedSheets() Dim Sht As Worksheet Dim ncUnprotect As New Collection For Each Sht In ActiveWindow.SelectedSheets ncUnprotect.Add Item:=Sht Next Sht Worksheets(1).Select For Each Sht In ncUnprotect Sht.Unprotect Next Sht End Sub I have assumed that you have NOT used passwords to protect your sheets. Ken Johnson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet Protection
Ken Johnson wrote: Hi Wally, The following macros worked for me... To protect some sheets first use Shift-Click Tab to group those sheets then run the following macro... Public Sub ProtectSelectedSheets() Dim Sht As Worksheet Dim ncProtect As New Collection For Each Sht In ActiveWindow.SelectedSheets ncProtect.Add Item:=Sht Next Sht Worksheets(1).Select For Each Sht In ncProtect Sht.Protect Next Sht End Sub To Unprotect some protected sheets first use Shift-Click Tab to group those sheets then run the following macro... Public Sub UnprotectSelectedSheets() Dim Sht As Worksheet Dim ncUnprotect As New Collection For Each Sht In ActiveWindow.SelectedSheets ncUnprotect.Add Item:=Sht Next Sht Worksheets(1).Select For Each Sht In ncUnprotect Sht.Unprotect Next Sht End Sub I have assumed that you have NOT used passwords to protect your sheets. Ken Johnson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet Protection
Ken, sorry I took so long in getting back. I can't get the macro to
work. Iv'e tried more than several times. Any suggestions? Wally Ken Johnson wrote: Hi Wally, The following macros worked for me... To protect some sheets first use Shift-Click Tab to group those sheets then run the following macro... Public Sub ProtectSelectedSheets() Dim Sht As Worksheet Dim ncProtect As New Collection For Each Sht In ActiveWindow.SelectedSheets ncProtect.Add Item:=Sht Next Sht Worksheets(1).Select For Each Sht In ncProtect Sht.Protect Next Sht End Sub To Unprotect some protected sheets first use Shift-Click Tab to group those sheets then run the following macro... Public Sub UnprotectSelectedSheets() Dim Sht As Worksheet Dim ncUnprotect As New Collection For Each Sht In ActiveWindow.SelectedSheets ncUnprotect.Add Item:=Sht Next Sht Worksheets(1).Select For Each Sht In ncUnprotect Sht.Unprotect Next Sht End Sub I have assumed that you have NOT used passwords to protect your sheets. Ken Johnson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet Protection
Hi wally,
Are you ofay with macros or a newbie? What exactly happens? Is your security level set at medium? When you open your workbook do you get the Security Warning dialog which gives you the option of Enabling Macros or Disabling Macros? To change security level to medium go ToolsMacroSecurity then select MediumOKClose the workbookreopen the workbook. If you do get that dialog are you clicking on Enable macros? Was I correct in assuming you are NOT using a password with your protection? It's unlikely to be a version issue, it works on my PCs with XL 2003 and my old iMac with OS 9.2 Let me know how you go. The code works perfectly my end. I'll be using it myself. Thanks for the idea. Ken Johnson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet Protection
I have xp2003. I am familiar with macros, I use them in more than one
workbook and sheets. I do not get any security level warning dialog. I can access the macro function anytime I request it. I am not using a password. You give me the VBA(?) that will perform the function I want. Now I don't understand exactly what I have to do with it to make it work. Do I click the record macro button and then type in the info, I'm just confused. Thanks Ken Johnson wrote: Hi wally, Are you ofay with macros or a newbie? What exactly happens? Is your security level set at medium? When you open your workbook do you get the Security Warning dialog which gives you the option of Enabling Macros or Disabling Macros? To change security level to medium go ToolsMacroSecurity then select MediumOKClose the workbookreopen the workbook. If you do get that dialog are you clicking on Enable macros? Was I correct in assuming you are NOT using a password with your protection? It's unlikely to be a version issue, it works on my PCs with XL 2003 and my old iMac with OS 9.2 Let me know how you go. The code works perfectly my end. I'll be using it myself. Thanks for the idea. Ken Johnson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet Protection
Hi Wally,
1. Copy this... Public Sub ProtectSelectedSheets() Dim Sht As Worksheet Dim ncProtect As New Collection For Each Sht In ActiveWindow.SelectedSheets ncProtect.Add Item:=Sht Next Sht Worksheets(1).Select For Each Sht In ncProtect Sht.Protect Next Sht End Sub 2.On your workbook, open the VBA Editor by pressing Alt + F11 3. In the VBA Editor go Insert Module then paste the code into the blank module that appears. 4.Come back here and copy this code... Public Sub UnprotectSelectedSheets() Dim Sht As Worksheet Dim ncUnprotect As New Collection For Each Sht In ActiveWindow.SelectedSheets ncUnprotect.Add Item:=Sht Next Sht Worksheets(1).Select For Each Sht In ncUnprotect Sht.Unprotect Next Sht End Sub 5. Go back to the VBA editor in your workbook, which should still be open at the new module, then paste in the code into the same module. 6. Go back to your worksheet by going Alt + F11 7. Save 8. To run either macro Shift-click or Ctrl-click sheet tabs to select the sheets to protect or unprotect, then go ToolsMacroMacros...select the appropriate macro then click the Run button. Let me know how you go. Ken Johnson |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet Protection
Ken,
Works like a charm, thank you very much. Wally Ken Johnson wrote: Hi Wally, 1. Copy this... Public Sub ProtectSelectedSheets() Dim Sht As Worksheet Dim ncProtect As New Collection For Each Sht In ActiveWindow.SelectedSheets ncProtect.Add Item:=Sht Next Sht Worksheets(1).Select For Each Sht In ncProtect Sht.Protect Next Sht End Sub 2.On your workbook, open the VBA Editor by pressing Alt + F11 3. In the VBA Editor go Insert Module then paste the code into the blank module that appears. 4.Come back here and copy this code... Public Sub UnprotectSelectedSheets() Dim Sht As Worksheet Dim ncUnprotect As New Collection For Each Sht In ActiveWindow.SelectedSheets ncUnprotect.Add Item:=Sht Next Sht Worksheets(1).Select For Each Sht In ncUnprotect Sht.Unprotect Next Sht End Sub 5. Go back to the VBA editor in your workbook, which should still be open at the new module, then paste in the code into the same module. 6. Go back to your worksheet by going Alt + F11 7. Save 8. To run either macro Shift-click or Ctrl-click sheet tabs to select the sheets to protect or unprotect, then go ToolsMacroMacros...select the appropriate macro then click the Run button. Let me know how you go. Ken Johnson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet Protection
That's great Wally!
Thanks for the feedback. If you haven't already, you might want to look into storing them in your Personal Macro Workbook so that they are available for use in all your workbooks. Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup function and compare | Excel Discussion (Misc queries) | |||
Cell but not sheet protection | Excel Discussion (Misc queries) | |||
Copying multiple sheets from one book 2 another and undertake spec | Excel Discussion (Misc queries) | |||
Filter Switches vs Sheet Protection? | Excel Discussion (Misc queries) | |||
Naming & renaming a sheet tab | Excel Worksheet Functions |