Making changes to most worksheets?

[jonco]

I have a workbook where I want to make some cell protection changes on most
of the sheets. There are about 100 sheets so I'd rather not do them
individually.

I want to unprotect certain cells so that when the sheet is 'protected'
these cells can still be used for entry and pasting.

Here's what I have so far, but it's not working: (copied from another
workbook and modified)

'TempProtect ()

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets

Range("I2,J2,L2,N2,O2:P2,A35:T36,A35").Select
IF Worksheet.name = "Index" Then Next (I want it to skip this
worksheet)
IF Worksheet.name = "Trans" Then Next (I want it to skip this
worksheet also)
IF Worksheet.name = "Customers" Then Next (I want it to skip this
worksheet too)
Selection.Locked = False
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

Next

End Sub

Any help you can give me is appreciated.
Jonco

[tony h]

try this:


Sub TempProtect()

Dim ws As Worksheet
Dim rng As Range

For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "Index", "Trans", "Customers"
'don't do anything
Case Else
Set rng = ws.Range("I2,J2,L2,N2,O2:P2,A35:T36,A35")
rng.Locked = False
rng.FormulaHidden = False
ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Select

Next
MsgBox "done"
End Sub

regards

[Tim Williams]

'******************************
Sub TempProtect()

Dim ws As Worksheet
Dim s As String

For Each ws In ThisWorkbook.Worksheets
s = ws.Name
If Not (s = "Index" Or s = "Trans" Or s = "Customers") Then
With ws.Range("I2,J2,L2,N2,O2:P2,A35:T36,A35")
.Locked = False
.FormulaHidden = False
End With
ws.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True

End If
Next ws
End Sub
'*****************************


Tim



"jonco" <jonco48@sbcglobal.net> wrote in message news:RpApg.76248$4L1.50104@newssvr11.news.prodigy.com...
>I have a workbook where I want to make some cell protection changes on most of the sheets. There are about 100 sheets so I'd
>rather not do them individually.
>
> I want to unprotect certain cells so that when the sheet is 'protected' these cells can still be used for entry and pasting.
>
> Here's what I have so far, but it's not working: (copied from another workbook and modified)
>
> 'TempProtect ()
>
> Dim ws As Worksheet
> For Each ws In ThisWorkbook.Worksheets
>
> Range("I2,J2,L2,N2,O2:P2,A35:T36,A35").Select
> IF Worksheet.name = "Index" Then Next (I want it to skip this worksheet)
> IF Worksheet.name = "Trans" Then Next (I want it to skip this worksheet also)
> IF Worksheet.name = "Customers" Then Next (I want it to skip this worksheet too)
> Selection.Locked = False
> Selection.FormulaHidden = False
> ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
>
> Next
>
> End Sub
>
> Any help you can give me is appreciated.
> Jonco
>

[jonco]

Thanks guys, I'll give these a try. I appreciate it.

Jonco


"jonco" <jonco48@sbcglobal.net> wrote in message
news:RpApg.76248$4L1.50104@newssvr11.news.prodigy.com...
>I have a workbook where I want to make some cell protection changes on most
>of the sheets. There are about 100 sheets so I'd rather not do them
>individually.
>
> I want to unprotect certain cells so that when the sheet is 'protected'
> these cells can still be used for entry and pasting.
>
> Here's what I have so far, but it's not working: (copied from another
> workbook and modified)
>
> 'TempProtect ()
>
> Dim ws As Worksheet
> For Each ws In ThisWorkbook.Worksheets
>
> Range("I2,J2,L2,N2,O2:P2,A35:T36,A35").Select
> IF Worksheet.name = "Index" Then Next (I want it to skip this
> worksheet)
> IF Worksheet.name = "Trans" Then Next (I want it to skip this
> worksheet also)
> IF Worksheet.name = "Customers" Then Next (I want it to skip this
> worksheet too)
> Selection.Locked = False
> Selection.FormulaHidden = False
> ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
> Scenarios:=True
>
> Next
>
> End Sub
>
> Any help you can give me is appreciated.
> Jonco
>

[jonco]

I'm trying to make some changes to all but three sheets in amy workbook.
I got the following macro here, but it's still not working.

Any help will be appreciated.
I'm getting the following error:

Run-time Error: 1004 Unable to set the Locked property of the Range class

Sub TempProtect()
Dim ws As Worksheet
Dim rng As Range
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "Index", "Trans", "Customers"
'don't do anything
Case Else
Set rng = ws.Range("I2,J2,L2,N2,O2:P2,A35:T36,A35")
rng.Locked = False ' ************************************ This is where
the error is
rng.FormulaHidden = False
ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Select
Next
MsgBox "done"
End Sub

Thanks for any help I can get.
Jonco


"jonco" <jonco48@sbcglobal.net> wrote in message
news:RpApg.76248$4L1.50104@newssvr11.news.prodigy.com...
>I have a workbook where I want to make some cell protection changes on most
>of the sheets. There are about 100 sheets so I'd rather not do them
>individually.
>
> I want to unprotect certain cells so that when the sheet is 'protected'
> these cells can still be used for entry and pasting.
>
> Here's what I have so far, but it's not working: (copied from another
> workbook and modified)
>
> 'TempProtect ()
>
> Dim ws As Worksheet
> For Each ws In ThisWorkbook.Worksheets
>
> Range("I2,J2,L2,N2,O2:P2,A35:T36,A35").Select
> IF Worksheet.name = "Index" Then Next (I want it to skip this
> worksheet)
> IF Worksheet.name = "Trans" Then Next (I want it to skip this
> worksheet also)
> IF Worksheet.name = "Customers" Then Next (I want it to skip this
> worksheet too)
> Selection.Locked = False
> Selection.FormulaHidden = False
> ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
> Scenarios:=True
>
> Next
>
> End Sub
>
> Any help you can give me is appreciated.
> Jonco
>

[tony h]

Is it all worksheets or just some?

do A ?ws.name in the immediate pane to find out.

I would guess it might be something to do with the worksheet already being protected.

Sorry I can't investigate further ... got work to do

[jonco]

I got it to work. I think the problem was that I was referencing the same
call twice in the range. Once I eliminated that it worked.

Thanks for responding.

Jon

"jonco" <jonco48@sbcglobal.net> wrote in message
news:iUUpg.114339$H71.93445@newssvr13.news.prodigy.com...
> I'm trying to make some changes to all but three sheets in amy workbook. I
> got the following macro here, but it's still not working.
>
> Any help will be appreciated.
> I'm getting the following error:
>
> Run-time Error: 1004 Unable to set the Locked property of the Range class
>
> Sub TempProtect()
> Dim ws As Worksheet
> Dim rng As Range
> For Each ws In ThisWorkbook.Worksheets
> Select Case ws.Name
> Case "Index", "Trans", "Customers"
> 'don't do anything
> Case Else
> Set rng = ws.Range("I2,J2,L2,N2,O2:P2,A35:T36,A35")
> rng.Locked = False ' ************************************ This is
> where the error is
> rng.FormulaHidden = False
> ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
> End Select
> Next
> MsgBox "done"
> End Sub
>
> Thanks for any help I can get.
> Jonco
>
>
> "jonco" <jonco48@sbcglobal.net> wrote in message
> news:RpApg.76248$4L1.50104@newssvr11.news.prodigy.com...
>>I have a workbook where I want to make some cell protection changes on
>>most of the sheets. There are about 100 sheets so I'd rather not do them
>>individually.
>>
>> I want to unprotect certain cells so that when the sheet is 'protected'
>> these cells can still be used for entry and pasting.
>>
>> Here's what I have so far, but it's not working: (copied from another
>> workbook and modified)
>>
>> 'TempProtect ()
>>
>> Dim ws As Worksheet
>> For Each ws In ThisWorkbook.Worksheets
>>
>> Range("I2,J2,L2,N2,O2:P2,A35:T36,A35").Select
>> IF Worksheet.name = "Index" Then Next (I want it to skip this
>> worksheet)
>> IF Worksheet.name = "Trans" Then Next (I want it to skip this
>> worksheet also)
>> IF Worksheet.name = "Customers" Then Next (I want it to skip this
>> worksheet too)
>> Selection.Locked = False
>> Selection.FormulaHidden = False
>> ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
>> Scenarios:=True
>>
>> Next
>>
>> End Sub
>>
>> Any help you can give me is appreciated.
>> Jonco
>>
>
>