Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for worksheet protection
Hi,
Need help with macro code to run a passworded worksheet 'Protect' and one to 'Unprotect' it. Thank you. Floss |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for worksheet protection
Hi Floss
ActiveSheet.Unprotect Password:="me" ActiveSheet.Protect Password:="me" this unprotects / protects the activesheet when you want to specify the name of the sheet you can use ThisWorkbook.Worksheets("Sheet1).Unprotect("YourPa ssword") ThisWorkbook.Worksheets("Sheet1).Protect("YourPass word") Cheers julieD "Floss" wrote in message ... Hi, Need help with macro code to run a passworded worksheet 'Protect' and one to 'Unprotect' it. Thank you. Floss |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for worksheet protection
Hi Floos
How abour a custom function? Public Function unprotectSh(Sh As String) z = "YourPassword" unprotectSh = Sheets(Sh).Unprotect(z) End Function Public Function protectSh(Sh As String) z = "YourPassword" protectSh = Sheets(Sh).Protect(z) End Function And use functions in your module, ie: Sub Macro1() unprotectSh("Sheet1") 'Do some stuff... protectSh("Sheet1") End Sub HTH Cordially Pascal "Floss" a écrit dans le message de ... Hi, Need help with macro code to run a passworded worksheet 'Protect' and one to 'Unprotect' it. Thank you. Floss |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for worksheet protection
Hi,
what have I to do to protect all sheets in my workbook by checkbox? Tony €žJulieD" napÃ*sal (napÃ*sala): Hi Floss ActiveSheet.Unprotect Password:="me" ActiveSheet.Protect Password:="me" this unprotects / protects the activesheet when you want to specify the name of the sheet you can use ThisWorkbook.Worksheets("Sheet1).Unprotect("YourPa ssword") ThisWorkbook.Worksheets("Sheet1).Protect("YourPass word") Cheers julieD "Floss" wrote in message ... Hi, Need help with macro code to run a passworded worksheet 'Protect' and one to 'Unprotect' it. Thank you. Floss |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for worksheet protection
Tony:
Unless you really need a check box to select/deselect which sheets to protect/unprotect, you may want to try the following - Each protects or unprotects all worksheets, as well as the workbook itself. I attached each one to an icon on a special tool bar, for easy accesss/operation. Hope this helps. (the following is from this user group, Not MY brainchild) BeeJay Sub DraftProtectionOff() Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.ProtectContents = True Then ws.Unprotect ("WSPassword") End If Next ActiveWorkbook.Unprotect (["WBPassword"]) Application.ScreenUpdating = True End Sub Sub DraftProtectionOn() Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.ProtectContents = False Then ws.Protect ("WSPassword") End If Next ActiveWorkbook.Protect (["WBPassword"]) Application.ScreenUpdating = True End Sub "Tony" wrote: Hi, what have I to do to protect all sheets in my workbook by checkbox? Tony €žJulieD" napÃ*sal (napÃ*sala): Hi Floss ActiveSheet.Unprotect Password:="me" ActiveSheet.Protect Password:="me" this unprotects / protects the activesheet when you want to specify the name of the sheet you can use ThisWorkbook.Worksheets("Sheet1).Unprotect("YourPa ssword") ThisWorkbook.Worksheets("Sheet1).Protect("YourPass word") Cheers julieD "Floss" wrote in message ... Hi, Need help with macro code to run a passworded worksheet 'Protect' and one to 'Unprotect' it. Thank you. Floss |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for worksheet protection
Thank you BeeJay,
with a little modification now it realy works. Here is how it looks: Sub Workbook_protectiona() Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If Sheets("Entry").CheckBoxes("cbProtectSheet").Value = xlOn Then ws.Protect DrawingObjects:=True, Contents:=True Else ws.Unprotect End If Next End Sub Tony €žBEEJAY" napÃ*sal (napÃ*sala): Tony: Unless you really need a check box to select/deselect which sheets to protect/unprotect, you may want to try the following - Each protects or unprotects all worksheets, as well as the workbook itself. I attached each one to an icon on a special tool bar, for easy accesss/operation. Hope this helps. (the following is from this user group, Not MY brainchild) BeeJay Sub DraftProtectionOff() Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.ProtectContents = True Then ws.Unprotect ("WSPassword") End If Next ActiveWorkbook.Unprotect (["WBPassword"]) Application.ScreenUpdating = True End Sub Sub DraftProtectionOn() Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets If ws.ProtectContents = False Then ws.Protect ("WSPassword") End If Next ActiveWorkbook.Protect (["WBPassword"]) Application.ScreenUpdating = True End Sub "Tony" wrote: Hi, what have I to do to protect all sheets in my workbook by checkbox? Tony €žJulieD" napÃ*sal (napÃ*sala): Hi Floss ActiveSheet.Unprotect Password:="me" ActiveSheet.Protect Password:="me" this unprotects / protects the activesheet when you want to specify the name of the sheet you can use ThisWorkbook.Worksheets("Sheet1).Unprotect("YourPa ssword") ThisWorkbook.Worksheets("Sheet1).Protect("YourPass word") Cheers julieD "Floss" wrote in message ... Hi, Need help with macro code to run a passworded worksheet 'Protect' and one to 'Unprotect' it. Thank you. Floss |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Protection- AKA: Sheet/Macro Password Protection | Setting up and Configuration of Excel | |||
use a macro to automate excel worksheet password protection | Excel Discussion (Misc queries) | |||
Cell Protection vs. Worksheet Protection | Excel Discussion (Misc queries) | |||
Worksheet protection is gone and only wokbook protection can be se | Excel Discussion (Misc queries) | |||
protection of worksheet | Excel Discussion (Misc queries) |