RE: Making changes to worksheets problem macro

[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

[[email protected]]

jonco skrev:

> 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




Hi!

I think you have to unlock the sheet before you lock the range, just as
a precaution. If you run it once from scratch it'll work fine, but the
next time you try it, the sheet is locked.
Try inserting this as the first row in Case Else:
ws.unprotect

I tried your code with this and I think it worked!

/David

[jonco]

I finally got it to work... after I figured out I was referencing the same
cell twice in the Range statement (A;35)

Thank you very much!
Jonco


"jonco" <[email protected]> wrote in message
news:[email protected]...
> 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
>