Macro to Select a group of sheets and protect them with password

[Steve Klenner]

This macro used to work...I am now using Excel 2003....it appears to fail at
line ... sht.Protect password="lock"

I want to be able to select certain sheets within a workbook and protect
them with a password.

I'd like to have a subsequent macro that would do the opposite....select
same sheets and un-protect them.

*******************************
Sub ProtectAllSheets()
Sheets("Sheet1").Select
Sheets("Sheet2").Select
Sheets("Sheet9").Select
Sheets("Sheet11").Select
For Each sht In Sheets
sht.Protect Password:="lock"
Next sht
End Sub
*******************************
Thanks
Steve

 

[Ron de Bruin]

This macro used to work
I don't think so???

Sub ProtectAllSheets()
Dim sht As Worksheet
For Each sht In Sheets(Array("Sheet1", "Sheet2", "Sheet9", "Sheet11"))
sht.Protect Password:="lock"
Next sht
End Sub

Change sht.Protect Password:="lock"
to sht.UnProtect Password:="lock"

 

[Steve Klenner]

Ron -
Thanks Much.....that did the trick!
Steve

I don't think so???

Sub ProtectAllSheets()
Dim sht As Worksheet
For Each sht In Sheets(Array("Sheet1", "Sheet2", "Sheet9", "Sheet11"))
sht.Protect Password:="lock"
Next sht
End Sub

Change sht.Protect Password:="lock"
to sht.UnProtect Password:="lock"

 

[Lydia Cochran]

I, too, have an issue with code that works in Excel2000,
but does not work in Excel2003. I was told that the fact
that it worked in 2000 was a bug. Anyway, this is the code:

Private Sub Workbook_open()
If Worksheets("sheet1").Protect = False Then
Worksheets("sheet1").Range("g16:g19").Locked = False
Worksheets("sheet1").Range("h16:h19").Locked = False
Worksheets("sheet1").Range("h25:h29").Locked = False
Worksheets("sheet1").Range("g31:g47").Locked = False
Worksheets("sheet1").Protect ("xxxxxx"), Contents:=True,
Objects:=True, Scenarios:=True
End If
ActiveWorkbook.Protect ("xxxxx"), Structure:=True,
Windows:=False
Range("A1").Select
End Sub

If anyone knows a way to get this code to work in 2003, I
would really appreciate the advice.

Thanks

-----Original Message-----
I don't think so???

Sub ProtectAllSheets()
Dim sht As Worksheet
For Each sht In Sheets(Array

("Sheet1", "Sheet2", "Sheet9", "Sheet11"))

sht.Protect Password:="lock"
Next sht
End Sub

Change sht.Protect Password:="lock"
to sht.UnProtect Password:="lock"

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




"Steve Klenner" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

Try this one Lydia

Look at the changes in the code

Private Sub Workbook_open()
If Worksheets("sheet1").ProtectContents = False Then
Worksheets("sheet1").Range("g16:g19,h16:h19,h25:h29,g31:g47").Locked = False
Worksheets("sheet1").Protect "xxxxx", Scenarios:=True, _
DrawingObjects:=True, Contents:=True
End If
ActiveWorkbook.Protect "xxxxx", Structure:=True, Windows:=False
Range("A1").Select
End Sub

 

[Lydia Cochran]

Works great in 2003. I hope it works in 2000 as well
because we have people using both. I'll test that later.

I had another problem arise. Can you (or anyone) help with
a inaccessible commandbuton? I can't remember if it was
something I did or not, but 2 out of 8 commandbuttons on
my spreadsheet cannot be selected in design mode. The 2
inaccessible buttons work when clicked on in regular mode,
but in design mode I cannot even call them up in the
properties window. They are not in the properties dropdown
list. Any ideas?

Thanks,
Lydia

-----Original Message-----
Try this one Lydia

Look at the changes in the code

Private Sub Workbook_open()
If Worksheets("sheet1").ProtectContents = False Then
Worksheets("sheet1").Range

("g16:g19,h16:h19,h25:h29,g31:g47").Locked = False

Worksheets("sheet1").Protect "xxxxx", Scenarios:=True, _
DrawingObjects:=True, Contents:=True
End If
ActiveWorkbook.Protect "xxxxx", Structure:=True, Windows:=False
Range("A1").Select
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




"Lydia Cochran" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

Maybe they are Forms Buttons

Right click on the button and look if you see
Format control
Assign Macro

 

[Guest]

That is part of the problem. I can't even right click on
the buttons. It is almost like they are not there in
design mode. Like I said, they work fine in regular mode.
I may have to try a different machine or Excel version. I
can't think of anything else to do.

Thanks anyway!

-----Original Message-----
Maybe they are Forms Buttons

Right click on the button and look if you see
Format control
Assign Macro

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




"Lydia Cochran" <[email protected]> wrote in

message news:[email protected]...