Protect_Unprotect Macro modification.

[Colin Hayes]

Hi

I use this macro to protect and unprotect all the worksheets in my
workbook :

Protect_Unprotect Macro
' Shortcut Ctrl + z
' Protects / Unprotects by turn all sheets in a workbook

'
Const PWORD As String = "12071956"
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
If .ProtectContents Then
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
Else
wkSht.Protect Password:=PWORD
statStr = statStr & ": Protected"
End If
End With
Next wkSht
MsgBox Mid(statStr, 2)


'
End Sub


it works very well , but unfortunately resets all the protection
parameters to the minimum. I need to add a line to it so that it will
maintain formatting of rows in the protected sheet.

AllowFormattingRows:=True

but I'm not sure where i can incorporate it into the code. Can someone
help?


Thanks.

 

[Dave Peterson]

I think that the safest thing to try is to record a macro when you protect a
worksheet and toggle the settings that you want checked.

You'll see the code you need to add to your code.

If you have trouble incorporating the changes, post the recorded macro code and
I'm sure you'll get help.

 

[Colin Hayes]

Hi Dave

Thanks for getting back to me on this. I'm grateful for your advice ,
and did think that what you suggest was in fact exactly what I'm doing.
I'm posting my existing code , with the additional code I need to
incorporate and asking if anyone with greater expertise can help marry
the two.

My existing code actually undoes the toggled settings. I have the code
to produce the settings I need , I just don't know how to put them into
my macro so that the settings remain in place when I run it.

Sorry if I wasn't clear before.

^_^

 

[Dave Peterson]

I'm not sure which ones you want turned back on:

wkSht.Protect Password:=PWORD

Wksht.Protect password:=PWORD DrawingObjects:=False, Contents:=True, _
Scenarios:= True, AllowFormattingColumns:=True, _
AllowInsertingColumns:=True, AllowInsertingRows:=True, _
AllowDeletingRows:=True, AllowFiltering:=True, AllowFormattingRows:=True

I just chose those at random.

 

[Gord Dibben]

Colin

Your code toggles protection on/off each sheet so you will have various states
of protection unless you set all sheets first to protected or unprotected.

Is this what you want? Or do you want to protect or unprotect all sheets
together?

The AllowFormattingRows and a couple of other modes have been added below.

Also, CTRL + z is the default Excel UnDo shortcut key. You may want to try
another combo.

Sub Protect_Unprotect()
' Shortcut Ctrl + z
' Protects / Unprotects by turn all sheets in a workbook

'
Const PWORD As String = "12071956"
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
If .ProtectContents Then
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
Else
wkSht.Protect Password:=PWORD, DrawingObjects:=True, _
Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingRows:=True
statStr = statStr & ": Protected"
End If
End With
Next wkSht
MsgBox Mid(statStr, 2)

End Sub


Gord Dibben MS Excel MVP

 

[Colin Hayes]

Hi Gord

Thanks for your help. Still no joy I'm afraid.

I have a workbook of 25 sheets , and I use the macro to unprotect /
protect them all at one go. They are all either protect or unprotected
together and the macro toggles them.

I want to have row formatting available at all times , whether the sheet
is protected of unprotected. I need to be able to hide / unhide rows at
all times.

I find that if I set this manually for a sheet then it works , and
allows me to format rows in protected and unprotected state. The row
formatting commands are available. This is how I want it.

However , after I run the macro below , the ability to format rows is
gone. The row formatting commands are greyed out.

I just need the macro to be amended so it won't overwrite the row
formatting permission. I did try your amendment below , but couldn't
make it work.

So , that's it. Hope you can help.

Thanks.

 

[Colin Hayes]

I'm not sure which ones you want turned back on:

wkSht.Protect Password:=PWORD

Wksht.Protect password:=PWORD DrawingObjects:=False, Contents:=True, _
Scenarios:= True, AllowFormattingColumns:=True, _
AllowInsertingColumns:=True, AllowInsertingRows:=True, _
AllowDeletingRows:=True, AllowFiltering:=True, AllowFormattingRows:=True

I just chose those at random.

Hi Dave

Ok Thanks. I'm not sure where to place this in the macro - I'm getting
red errors when I try to paste it in. If you could advise on that I'd be
grateful.

It's just row formatting i need switched on.

Thanks




Are you aware that we also buy CDs, Vinyl and DVDs? Send your lists of
unwanted items to (e-mail address removed) and we’ll quote you a price…


You can browse and buy direct from my full list of items at these addresses :

http://s1.amazon.co.uk/exec/varzea/ts/my-zshop/SPJK3X6KOJZR6/026-3393902-9
050050

or:

http://www.CDandVinyl.co.uk

or :

http://www.netsoundsmusic.com/chayes

or:

http://chayes.musicstack.com



To DOWNLOAD the full catalogue click here :


http://www.chayes.demon.co.uk/CDandVinyl_full_catalogue.exe



Best Wishes ,

Colin Hayes.



TEL / FAX : (UK) (0)208 804 9181

 

[Dave Peterson]

Replace this line:
wkSht.Protect Password:=PWORD
with
wkSht.Protect Password:=PWORD, AllowFormattingRows:=True

 

[Colin Hayes]

Replace this line:
wkSht.Protect Password:=PWORD
with
wkSht.Protect Password:=PWORD, AllowFormattingRows:=True

Hi Dave

Thanks for getting back. Still no joy , I'm afraid.

I'm highlighting all the rows on the sheet. I run the macro to unprotect
the sheet , and right click. The Hide and Unhide switches in the popup
are activated and in black.

I run the macro to protect the sheet again. Now when I right click the
Hide and unhide switched are greyed out. I need these to be accessible
when the sheet is protected , and would have thought that your
suggestion would have worked.

hmmm - bit stuck now.


This is how it is with your line in place :

' Protects / Unprotects by turn all sheets in a workbook

'
Const PWORD As String = "12071956"
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
If .ProtectContents Then
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
Else
wkSht.Protect Password:=PWORD, AllowFormattingRows:=True
statStr = statStr & ": Protected"
End If
End With
Next wkSht
MsgBox Mid(statStr, 2)


'
End Sub


I can't see why this wouldn't work.

Thanks again.

 

[Dave Peterson]

Record a macro when you protect the worksheet with your settings the way you
want.

Post that recorded code.

 

[Colin Hayes]

Record a macro when you protect the worksheet with your settings the way you
want.

Post that recorded code.

Hi Dave

OK will do.

Thanks again.

 

[Gord Dibben]

This works for me...........note the addition of of being able to select locked
cells.

Sub Protect_Unprotect()
' Shortcut Ctrl + z
' Protects / Unprotects by turn all sheets in a workbook

'
Const PWORD As String = "12071956"
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
If .ProtectContents Then
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
Else
wkSht.Protect Password:=PWORD, DrawingObjects:=True, _
Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingRows:=True
ActiveSheet.EnableSelection = xlNoRestrictions
statStr = statStr & ": Protected"
End If
End With
Next wkSht
MsgBox Mid(statStr, 2)

End Sub


Gord

 

[Colin Hayes]

Hi Gord

Thanks for helping on this. I think my machine must be set differently
somehow. Running the macro still switches of all the formatting I need
to stay open on protection. It's a real mystery to me why it continues
to do it , giving the extra code incorporated into it.

I run the macro to unprotect and the row formatting is available , right
clicking on highlighted rows shows this. Run the macro to protect and
it's all greyed out.

If I unprotect / protect manually then all the formatting remains
available , but not if I use the macro. Very strange. I'm clearly
missing something here.

I'm grateful for your efforts over it. Excel can be a very mysterious
program sometimes. I'll have a longer play with it , and I'll let you
know how it goes!



Best Wishes