spread sheet protection over several tabs
 

i would like to be able to turn worksheet protection on and off over several
tabs at one time in a workbook. i have the same range of protected cells on
each tab and in order to edit or change the ranges accross several tabs i
have to turn the protection off by tab. is there a way to select several
tabs at one time and turn protection off for the selected tabs?

Dale...

spread sheet protection over several tabs
 

You may be able to use a macro.

Select the sheets first (click on the first and ctrl-click on subsequent). But
remember to ungroup those sheets after the macro runs. Otherwise, you'll be
making the same change on each of the selected sheets. This can be very useful
for headers/descriptions. Not so much for real data.

Then use a macro and if you use the same password for all the sheets, it would
look something like:

Option Explicit
Sub UnprotectAll()
Dim wks As Worksheet
Dim pwd As String
pwd = InputBox(Prompt:="What's the password, Kenny?")

If Trim(pwd) = "" Then
Exit Sub
End If

For Each wks In ActiveWindow.SelectedSheets
With wks
If .ProtectContents = True _
Or .ProtectDrawingObjects = True _
Or .ProtectScenarios = True Then
On Error Resume Next
.Unprotect Password:=pwd
If Err.Number < 0 Then
MsgBox "Something went wrong with: " & wks.Name
Err.Clear
'exit for 'stop trying???
End If
On Error GoTo 0
End If
End With
Next wks

End Sub
Sub ProtectAll()
Dim wks As Worksheet
Dim pwd As String
pwd = InputBox(Prompt:="What's the password, Kenny?")

If Trim(pwd) = "" Then
Exit Sub
End If

For Each wks In ActiveWindow.SelectedSheets
With wks
If .ProtectContents = True _
Or .ProtectDrawingObjects = True _
Or .ProtectScenarios = True Then
'do nothing, it's already protected
Else
On Error Resume Next
.Protect Password:=pwd
If Err.Number < 0 Then
MsgBox "Something went wrong with: " & wks.Name
Err.Clear
'exit for 'stop trying???
End If
On Error GoTo 0
End If
End With
Next wks
End Sub

Then back to excel and save this workbook with a nice name.

Anytime you want to unprotect or protect all the worksheets in any workbook, you
can open this file.

Then activate the workbook that you want to make changes to.
Hit alt-f8
Select the macro
and click Run

If you really wanted, you could embed the password directly in the code (both
procedures) and not be bothered with a prompt.

Change this line:

pwd = InputBox(Prompt:="What's the password, Kenny?")
to
pwd = "TopSecretPaSsWord1234_x"


If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)



Dale... wrote:

i would like to be able to turn worksheet protection on and off over several
tabs at one time in a workbook. i have the same range of protected cells on
each tab and in order to edit or change the ranges accross several tabs i
have to turn the protection off by tab. is there a way to select several
tabs at one time and turn protection off for the selected tabs?

Dale...

--

Dave Peterson

spread sheet protection over several tabs
 

Dave,
Once you select more than one tab the "Unprotect Sheet" option is not
available. How whould that work in a Macro? and i not using passwords.
Dale...

"Dale..." wrote:

i would like to be able to turn worksheet protection on and off over several
tabs at one time in a workbook. i have the same range of protected cells on
each tab and in order to edit or change the ranges accross several tabs i
have to turn the protection off by tab. is there a way to select several
tabs at one time and turn protection off for the selected tabs?

Dale...

spread sheet protection over several tabs
 

I didn't have any trouble with the unprotection (did you when you tested?). But
protecting did cause a problem.

I changed both subs -- just so the code looked consistent:

Option Explicit
Sub UnprotectAll()
Dim wks As Object
Dim SelSheets As Object

Set SelSheets = ActiveWindow.SelectedSheets
ActiveWindow.SelectedSheets(1).Select

For Each wks In SelSheets
With wks
If .ProtectContents = True _
Or .ProtectDrawingObjects = True _
Or .ProtectScenarios = True Then
On Error Resume Next
.Unprotect
If Err.Number < 0 Then
MsgBox "Something went wrong with: " & wks.Name
Err.Clear
'exit for 'stop trying???
End If
On Error GoTo 0
End If
End With
Next wks

End Sub
Sub ProtectAll()
Dim wks As Object
Dim SelSheets As Object

Set SelSheets = ActiveWindow.SelectedSheets
ActiveWindow.SelectedSheets(1).Select

For Each wks In SelSheets
With wks
If .ProtectContents = True _
Or .ProtectDrawingObjects = True _
Or .ProtectScenarios = True Then
'do nothing, it's already protected
Else
On Error Resume Next
.Protect
If Err.Number < 0 Then
MsgBox "Something went wrong with: " & wks.Name
Err.Clear
'exit for 'stop trying???
End If
On Error GoTo 0
End If
End With
Next wks
End Sub




Dale... wrote:

Dave,
Once you select more than one tab the "Unprotect Sheet" option is not
available. How whould that work in a Macro? and i not using passwords.
Dale...

"Dale..." wrote:

i would like to be able to turn worksheet protection on and off over several
tabs at one time in a workbook. i have the same range of protected cells on
each tab and in order to edit or change the ranges accross several tabs i
have to turn the protection off by tab. is there a way to select several
tabs at one time and turn protection off for the selected tabs?

Dale...

--

Dave Peterson

spread sheet protection over several tabs
 

thanks much Dave, this worked great...
where are you located?

"Dave Peterson" wrote:

I didn't have any trouble with the unprotection (did you when you tested?). But
protecting did cause a problem.

I changed both subs -- just so the code looked consistent:

Option Explicit
Sub UnprotectAll()
Dim wks As Object
Dim SelSheets As Object

Set SelSheets = ActiveWindow.SelectedSheets
ActiveWindow.SelectedSheets(1).Select

For Each wks In SelSheets
With wks
If .ProtectContents = True _
Or .ProtectDrawingObjects = True _
Or .ProtectScenarios = True Then
On Error Resume Next
.Unprotect
If Err.Number < 0 Then
MsgBox "Something went wrong with: " & wks.Name
Err.Clear
'exit for 'stop trying???
End If
On Error GoTo 0
End If
End With
Next wks

End Sub
Sub ProtectAll()
Dim wks As Object
Dim SelSheets As Object

Set SelSheets = ActiveWindow.SelectedSheets
ActiveWindow.SelectedSheets(1).Select

For Each wks In SelSheets
With wks
If .ProtectContents = True _
Or .ProtectDrawingObjects = True _
Or .ProtectScenarios = True Then
'do nothing, it's already protected
Else
On Error Resume Next
.Protect
If Err.Number < 0 Then
MsgBox "Something went wrong with: " & wks.Name
Err.Clear
'exit for 'stop trying???
End If
On Error GoTo 0
End If
End With
Next wks
End Sub




Dale... wrote:

Dave,
Once you select more than one tab the "Unprotect Sheet" option is not
available. How whould that work in a Macro? and i not using passwords.
Dale...

"Dale..." wrote:

i would like to be able to turn worksheet protection on and off over several
tabs at one time in a workbook. i have the same range of protected cells on
each tab and in order to edit or change the ranges accross several tabs i
have to turn the protection off by tab. is there a way to select several
tabs at one time and turn protection off for the selected tabs?

Dale...

--

Dave Peterson

spread sheet protection over several tabs
 

In the great midwest (USA).


Dale... wrote:

thanks much Dave, this worked great...
where are you located?

"Dave Peterson" wrote:

I didn't have any trouble with the unprotection (did you when you tested?). But
protecting did cause a problem.

I changed both subs -- just so the code looked consistent:

Option Explicit
Sub UnprotectAll()
Dim wks As Object
Dim SelSheets As Object

Set SelSheets = ActiveWindow.SelectedSheets
ActiveWindow.SelectedSheets(1).Select

For Each wks In SelSheets
With wks
If .ProtectContents = True _
Or .ProtectDrawingObjects = True _
Or .ProtectScenarios = True Then
On Error Resume Next
.Unprotect
If Err.Number < 0 Then
MsgBox "Something went wrong with: " & wks.Name
Err.Clear
'exit for 'stop trying???
End If
On Error GoTo 0
End If
End With
Next wks

End Sub
Sub ProtectAll()
Dim wks As Object
Dim SelSheets As Object

Set SelSheets = ActiveWindow.SelectedSheets
ActiveWindow.SelectedSheets(1).Select

For Each wks In SelSheets
With wks
If .ProtectContents = True _
Or .ProtectDrawingObjects = True _
Or .ProtectScenarios = True Then
'do nothing, it's already protected
Else
On Error Resume Next
.Protect
If Err.Number < 0 Then
MsgBox "Something went wrong with: " & wks.Name
Err.Clear
'exit for 'stop trying???
End If
On Error GoTo 0
End If
End With
Next wks
End Sub




Dale... wrote:

Dave,
Once you select more than one tab the "Unprotect Sheet" option is not
available. How whould that work in a Macro? and i not using passwords.
Dale...

"Dale..." wrote:

i would like to be able to turn worksheet protection on and off over several
tabs at one time in a workbook. i have the same range of protected cells on
each tab and in order to edit or change the ranges accross several tabs i
have to turn the protection off by tab. is there a way to select several
tabs at one time and turn protection off for the selected tabs?

Dale...

--

Dave Peterson


--

Dave Peterson

spread sheet protection over several tabs
 

If you were more specific you might get a free beer next time Dale passes
through<g


Gord

On Wed, 24 Jun 2009 11:22:22 -0500, Dave Peterson
wrote:

In the great midwest (USA).


Dale... wrote:

thanks much Dave, this worked great...
where are you located?

"Dave Peterson" wrote:

I didn't have any trouble with the unprotection (did you when you tested?). But
protecting did cause a problem.

I changed both subs -- just so the code looked consistent:

Option Explicit
Sub UnprotectAll()
Dim wks As Object
Dim SelSheets As Object

Set SelSheets = ActiveWindow.SelectedSheets
ActiveWindow.SelectedSheets(1).Select

For Each wks In SelSheets
With wks
If .ProtectContents = True _
Or .ProtectDrawingObjects = True _
Or .ProtectScenarios = True Then
On Error Resume Next
.Unprotect
If Err.Number < 0 Then
MsgBox "Something went wrong with: " & wks.Name
Err.Clear
'exit for 'stop trying???
End If
On Error GoTo 0
End If
End With
Next wks

End Sub
Sub ProtectAll()
Dim wks As Object
Dim SelSheets As Object

Set SelSheets = ActiveWindow.SelectedSheets
ActiveWindow.SelectedSheets(1).Select

For Each wks In SelSheets
With wks
If .ProtectContents = True _
Or .ProtectDrawingObjects = True _
Or .ProtectScenarios = True Then
'do nothing, it's already protected
Else
On Error Resume Next
.Protect
If Err.Number < 0 Then
MsgBox "Something went wrong with: " & wks.Name
Err.Clear
'exit for 'stop trying???
End If
On Error GoTo 0
End If
End With
Next wks
End Sub




Dale... wrote:

Dave,
Once you select more than one tab the "Unprotect Sheet" option is not
available. How whould that work in a Macro? and i not using passwords.
Dale...

"Dale..." wrote:

i would like to be able to turn worksheet protection on and off over several
tabs at one time in a workbook. i have the same range of protected cells on
each tab and in order to edit or change the ranges accross several tabs i
have to turn the protection off by tab. is there a way to select several
tabs at one time and turn protection off for the selected tabs?

Dale...

--

Dave Peterson

spread sheet protection over several tabs
 

Or a punch in the nose from the rest!

Gord Dibben wrote:

If you were more specific you might get a free beer next time Dale passes
through<g

Gord

On Wed, 24 Jun 2009 11:22:22 -0500, Dave Peterson
wrote:

In the great midwest (USA).


Dale... wrote:

thanks much Dave, this worked great...
where are you located?

"Dave Peterson" wrote:

I didn't have any trouble with the unprotection (did you when you tested?). But
protecting did cause a problem.

I changed both subs -- just so the code looked consistent:

Option Explicit
Sub UnprotectAll()
Dim wks As Object
Dim SelSheets As Object

Set SelSheets = ActiveWindow.SelectedSheets
ActiveWindow.SelectedSheets(1).Select

For Each wks In SelSheets
With wks
If .ProtectContents = True _
Or .ProtectDrawingObjects = True _
Or .ProtectScenarios = True Then
On Error Resume Next
.Unprotect
If Err.Number < 0 Then
MsgBox "Something went wrong with: " & wks.Name
Err.Clear
'exit for 'stop trying???
End If
On Error GoTo 0
End If
End With
Next wks

End Sub
Sub ProtectAll()
Dim wks As Object
Dim SelSheets As Object

Set SelSheets = ActiveWindow.SelectedSheets
ActiveWindow.SelectedSheets(1).Select

For Each wks In SelSheets
With wks
If .ProtectContents = True _
Or .ProtectDrawingObjects = True _
Or .ProtectScenarios = True Then
'do nothing, it's already protected
Else
On Error Resume Next
.Protect
If Err.Number < 0 Then
MsgBox "Something went wrong with: " & wks.Name
Err.Clear
'exit for 'stop trying???
End If
On Error GoTo 0
End If
End With
Next wks
End Sub




Dale... wrote:

Dave,
Once you select more than one tab the "Unprotect Sheet" option is not
available. How whould that work in a Macro? and i not using passwords.
Dale...

"Dale..." wrote:

i would like to be able to turn worksheet protection on and off over several
tabs at one time in a workbook. i have the same range of protected cells on
each tab and in order to edit or change the ranges accross several tabs i
have to turn the protection off by tab. is there a way to select several
tabs at one time and turn protection off for the selected tabs?

Dale...

--

Dave Peterson


--

Dave Peterson