Can anyone help me, I am trying to find a vba code that will allow mw
to select a number of excel worksheets to apply protection to in one
go.
I have a workbook with 50 w/sheets and i only need to protect 30 of
them can i get a code that will allow me to choose the relevant sheets
that I wish to protect without all of the worksheets in the workbook
being protected.
I used to have a macro that did this but my version of excel was
updated and I lost the macro on my personal.xls.

Can anyone help!

wrote:
Can anyone help me, I am trying to find a vba code that will allow mw
to select a number of excel worksheets to apply protection to in one
go.
I have a workbook with 50 w/sheets and i only need to protect 30 of
them can i get a code that will allow me to choose the relevant sheets
that I wish to protect without all of the worksheets in the workbook
being protected.
I used to have a macro that did this but my version of excel was
updated and I lost the macro on my personal.xls.

Can anyone help!

The following macros worked for me. Edit "password" to suit your
needs...

To protect some sheets first use Shift-Click Sheet tabs to group those
sheets for protection then run the following macro...

Public Sub ProtectSelectedSheets()
Dim Sht As Worksheet
Dim ncProtect As New Collection
For Each Sht In ActiveWindow.SelectedSheets
ncProtect.Add Item:=Sht
Next Sht
Worksheets(1).Select
For Each Sht In ncProtect
Sht.Protect "password"
Next Sht
End Sub

To Unprotect some protected sheets first use Shift-Click Sheet tabs of
sheets to group them for removal of protection then run the following
macro...

Public Sub UnprotectSelectedSheets()
Dim Sht As Worksheet
Dim ncUnprotect As New Collection
For Each Sht In ActiveWindow.SelectedSheets
ncUnprotect.Add Item:=Sht
Next Sht
Worksheets(1).Select
For Each Sht In ncUnprotect
Sht.Unprotect "password"
Next Sht
End Sub

Ken Johnson

Ken thank you very much they work excellent... much appreciate it.
I am being lazy, but is there any way that when you run the macro a
dialogue box would pop up prompting you to enter a password of your
chosing?

many thanks for your help its excellent!!!!!



Ken Johnson wrote:
wrote:
Can anyone help me, I am trying to find a vba code that will allow mw
to select a number of excel worksheets to apply protection to in one
go.
I have a workbook with 50 w/sheets and i only need to protect 30 of
them can i get a code that will allow me to choose the relevant sheets
that I wish to protect without all of the worksheets in the workbook
being protected.
I used to have a macro that did this but my version of excel was
updated and I lost the macro on my personal.xls.

Can anyone help!

The following macros worked for me. Edit "password" to suit your
needs...

To protect some sheets first use Shift-Click Sheet tabs to group those
sheets for protection then run the following macro...

Public Sub ProtectSelectedSheets()
Dim Sht As Worksheet
Dim ncProtect As New Collection
For Each Sht In ActiveWindow.SelectedSheets
ncProtect.Add Item:=Sht
Next Sht
Worksheets(1).Select
For Each Sht In ncProtect
Sht.Protect "password"
Next Sht
End Sub

To Unprotect some protected sheets first use Shift-Click Sheet tabs of
sheets to group them for removal of protection then run the following
macro...

Public Sub UnprotectSelectedSheets()
Dim Sht As Worksheet
Dim ncUnprotect As New Collection
For Each Sht In ActiveWindow.SelectedSheets
ncUnprotect.Add Item:=Sht
Next Sht
Worksheets(1).Select
For Each Sht In ncUnprotect
Sht.Unprotect "password"
Next Sht
End Sub

Ken Johnson

wrote:
Ken thank you very much they work excellent... much appreciate it.
I am being lazy, but is there any way that when you run the macro a
dialogue box would pop up prompting you to enter a password of your
chosing?

many thanks for your help its excellent!!!!!


Hi,

You're lazy? What about me! I had the audacity to sleep for six and a
half hour just before you sent your reply:-) I'm in Sydney Australia so
it was around 11:30 pm when your reply came through, just after I
retired for the evening, hence the delay. I guess you're now asleep,
adding to the delay.

Anyway, try these changes...

Public Sub ProtectSelectedSheets2()
Dim MyPassword As String
Dim Sht As Worksheet
Dim ncProtect As New Collection
For Each Sht In ActiveWindow.SelectedSheets
ncProtect.Add Item:=Sht
Next Sht
MyPassword = InputBox("Enter your password")
Worksheets(1).Select
For Each Sht In ncProtect
Sht.Protect MyPassword
Next Sht
End Sub

Public Sub UnprotectSelectedSheets2()
Dim MyPassword As String
Dim blnPasswordFail As Boolean
Dim Sht As Worksheet
Dim ncUnprotect As New Collection
MyPassword = InputBox("Enter the password to use")
For Each Sht In ActiveWindow.SelectedSheets
ncUnprotect.Add Item:=Sht
Next Sht
Worksheets(1).Select
For Each Sht In ncUnprotect
On Error GoTo WRONG_PASSWORD
Sht.Unprotect "password"
Next Sht
If blnPasswordFail Then
MsgBox "One or more Sheets not unprotected!" _
& vbNewLine & "Check their password(s)."
Exit Sub
End If
Exit Sub
WRONG_PASSWORD: blnPasswordFail = True: Resume Next
End Sub

It would be nice to have the password appear as *****, rather than the
actual text, as you enter it into the input box.
It would also be nice to have the password double checked before it is
used by the protection macro.

However, that will have to be my next project.

Hope you're happy with the way they are inspite of their deficiencies.

Ken Johnson