Protection Macro


L

Lois

Hi,
I have the following macro written in spreadsheets to protect them, however,
in some workbooks there are 12 month tabs & a 'workings' tab. in the
workings tab i require different functionality (e.g. inserting rows &
columns) than the 12 month tabs. How do i change the macro to reflect this?

Sub ProtectAll()
Dim sPassword As String
sPassword = "LMW5487"
If Application.InputBox( _
Prompt:="Please enter the password", _
Title:="Password", _
Type:=2) <> sPassword Then
MsgBox "Wrong password!"
Exit Sub
End If
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="LMW5487", DrawingObjects:=False, Contents:=True,
Scenarios:= _
True, AllowFormattingCells:=True, AllowSorting:=True,
AllowDeletingRows:=True, AllowInsertingRows:=True, AllowFormattingRows:=True,
AllowFiltering:= _
True, AllowFormattingColumns:=True
Next ws
End Sub
 
Ad

Advertisements

M

macropod

Hi Lois,

According to your code, inserting & deleting rows is already allowed. Presumably you want the 'workings' sheet to allow inserting &
deleting columns also. In that case:

Sub ProtectAll()
Dim sPassword As String
sPassword = "LMW5487"
If Application.InputBox(Prompt:="Please enter the password", Title:="Password", _
Type:=2) <> sPassword Then
MsgBox "Wrong password!"
End
End If
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "workings" Then
ws.Protect Password:="sPassword", DrawingObjects:=False, Contents:=True, _
Scenarios:=True, AllowFormattingCells:=True, AllowSorting:=True, _
AllowDeletingRows:=True, AllowInsertingRows:=True, AllowFormattingRows:=True, _
AllowFiltering:=True, AllowFormattingColumns:=True
Else
ws.Protect Password:="sPassword", DrawingObjects:=False, Contents:=True, _
Scenarios:=True, AllowFormattingCells:=True, AllowSorting:=True, _
AllowDeletingRows:=True, AllowInsertingRows:=True, AllowFormattingRows:=True, _
AllowFiltering:=True, AllowFormattingColumns:=True, AllowInsertingColumns:=True, _
AllowDeletingColumns:=True
End If
Next ws
End Sub

where 'workings' is the name of the worksheet to leave unprotected
 
M

macropod

Hi Lois,

According to your code, inserting & deleting rows is already allowed. Presumably you want the 'workings' sheet to allow inserting &
deleting columns also. In that case:

Sub ProtectAll()
Dim sPassword As String
sPassword = "LMW5487"
If Application.InputBox(Prompt:="Please enter the password", Title:="Password", _
Type:=2) <> sPassword Then
MsgBox "Wrong password!"
End
End If
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "workings" Then
ws.Protect Password:="sPassword", DrawingObjects:=False, Contents:=True, _
Scenarios:=True, AllowFormattingCells:=True, AllowSorting:=True, _
AllowDeletingRows:=True, AllowInsertingRows:=True, AllowFormattingRows:=True, _
AllowFiltering:=True, AllowFormattingColumns:=True
Else
ws.Protect Password:="sPassword", DrawingObjects:=False, Contents:=True, _
Scenarios:=True, AllowFormattingCells:=True, AllowSorting:=True, _
AllowDeletingRows:=True, AllowInsertingRows:=True, AllowFormattingRows:=True, _
AllowFiltering:=True, AllowFormattingColumns:=True, AllowInsertingColumns:=True, _
AllowDeletingColumns:=True
End If
Next ws
End Sub

where 'workings' is the name of the worksheet to leave unprotected
 
L

Lois

great thanks
--
Lois


macropod said:
Hi Lois,

According to your code, inserting & deleting rows is already allowed. Presumably you want the 'workings' sheet to allow inserting &
deleting columns also. In that case:

Sub ProtectAll()
Dim sPassword As String
sPassword = "LMW5487"
If Application.InputBox(Prompt:="Please enter the password", Title:="Password", _
Type:=2) <> sPassword Then
MsgBox "Wrong password!"
End
End If
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "workings" Then
ws.Protect Password:="sPassword", DrawingObjects:=False, Contents:=True, _
Scenarios:=True, AllowFormattingCells:=True, AllowSorting:=True, _
AllowDeletingRows:=True, AllowInsertingRows:=True, AllowFormattingRows:=True, _
AllowFiltering:=True, AllowFormattingColumns:=True
Else
ws.Protect Password:="sPassword", DrawingObjects:=False, Contents:=True, _
Scenarios:=True, AllowFormattingCells:=True, AllowSorting:=True, _
AllowDeletingRows:=True, AllowInsertingRows:=True, AllowFormattingRows:=True, _
AllowFiltering:=True, AllowFormattingColumns:=True, AllowInsertingColumns:=True, _
AllowDeletingColumns:=True
End If
Next ws
End Sub

where 'workings' is the name of the worksheet to leave unprotected

--
Cheers
macropod
[Microsoft MVP - Word]


Lois said:
Hi,
I have the following macro written in spreadsheets to protect them, however,
in some workbooks there are 12 month tabs & a 'workings' tab. in the
workings tab i require different functionality (e.g. inserting rows &
columns) than the 12 month tabs. How do i change the macro to reflect this?

Sub ProtectAll()
Dim sPassword As String
sPassword = "LMW5487"
If Application.InputBox( _
Prompt:="Please enter the password", _
Title:="Password", _
Type:=2) <> sPassword Then
MsgBox "Wrong password!"
Exit Sub
End If
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="LMW5487", DrawingObjects:=False, Contents:=True,
Scenarios:= _
True, AllowFormattingCells:=True, AllowSorting:=True,
AllowDeletingRows:=True, AllowInsertingRows:=True, AllowFormattingRows:=True,
AllowFiltering:= _
True, AllowFormattingColumns:=True
Next ws
End Sub
 
Ad

Advertisements

L

Lois

great thanks
--
Lois


macropod said:
Hi Lois,

According to your code, inserting & deleting rows is already allowed. Presumably you want the 'workings' sheet to allow inserting &
deleting columns also. In that case:

Sub ProtectAll()
Dim sPassword As String
sPassword = "LMW5487"
If Application.InputBox(Prompt:="Please enter the password", Title:="Password", _
Type:=2) <> sPassword Then
MsgBox "Wrong password!"
End
End If
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "workings" Then
ws.Protect Password:="sPassword", DrawingObjects:=False, Contents:=True, _
Scenarios:=True, AllowFormattingCells:=True, AllowSorting:=True, _
AllowDeletingRows:=True, AllowInsertingRows:=True, AllowFormattingRows:=True, _
AllowFiltering:=True, AllowFormattingColumns:=True
Else
ws.Protect Password:="sPassword", DrawingObjects:=False, Contents:=True, _
Scenarios:=True, AllowFormattingCells:=True, AllowSorting:=True, _
AllowDeletingRows:=True, AllowInsertingRows:=True, AllowFormattingRows:=True, _
AllowFiltering:=True, AllowFormattingColumns:=True, AllowInsertingColumns:=True, _
AllowDeletingColumns:=True
End If
Next ws
End Sub

where 'workings' is the name of the worksheet to leave unprotected

--
Cheers
macropod
[Microsoft MVP - Word]


Lois said:
Hi,
I have the following macro written in spreadsheets to protect them, however,
in some workbooks there are 12 month tabs & a 'workings' tab. in the
workings tab i require different functionality (e.g. inserting rows &
columns) than the 12 month tabs. How do i change the macro to reflect this?

Sub ProtectAll()
Dim sPassword As String
sPassword = "LMW5487"
If Application.InputBox( _
Prompt:="Please enter the password", _
Title:="Password", _
Type:=2) <> sPassword Then
MsgBox "Wrong password!"
Exit Sub
End If
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="LMW5487", DrawingObjects:=False, Contents:=True,
Scenarios:= _
True, AllowFormattingCells:=True, AllowSorting:=True,
AllowDeletingRows:=True, AllowInsertingRows:=True, AllowFormattingRows:=True,
AllowFiltering:= _
True, AllowFormattingColumns:=True
Next ws
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top