Macro for worksheet protection
 

Hi,

Need help with macro code to run a passworded worksheet 'Protect' and one to 'Unprotect' it.

Thank you.
Floss

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

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

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

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

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