asked on

Protect Specifc Worksheets in a WorkBook,

Hi, I'm looking to protect on specific names sheets in a workbook and trying to use the following code, but it keeps throwing up an error.

Sub AddProtection()
Dim oWs As Worksheet
Set WSArray = Sheets(Array("Sheet1", "Sheet3", "Sheet40", "Sheet23"))
    For Each oWs In WSArray
            oWs.Protect Password:="1234", UserInterFaceOnly:=True, Contents:=True, AllowFormattingRows:=True
    Next oWs
End Sub

Open in new window

Thanks
Nev

Bill Prew

What error?

~bp

collinsn

ASKER

Sorry -

I get Run-time Error 9
Subscript out of Range and the code stops at this row when stepping through,

Set WSArray = Sheets(Array("Sheet1", "Sheet3", "Sheet40", "Sheet23"))

Thx
Nev

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

collinsn

ASKER

Hi,

This will not work for me, as I've changed the Sheet Names to be more descriptive. I need to reference the hard sheet1,2,3,4,5 names instead of the names I've given.

Thx
Nev

Bill Prew

Just put the descriptive sheet names in the Array.

~bp

Subodh Tiwari (Neeraj)

You may try it like this.....

Sub AddProtection()
Dim oWs
WSArray = Array(Sheet1, Sheet3, Sheet40, Sheet23)
    For Each oWs In WSArray
            oWs.Protect Password:="1234", UserInterFaceOnly:=True, Contents:=True, AllowFormattingRows:=True
    Next oWs
End Sub

Open in new window

collinsn

ASKER

Thank you, just what I needed.

Bill Prew

Welcome.

~bp

Subodh Tiwari (Neeraj)

@collinsn
Which solution worked for you?