PDA

View Full Version : [SOLVED] Worksheet Protection with exceptions



K. Georgiadis
02-12-2005, 08:37 AM
Here I go again, wishing to expand the list of exceptions in the following code which I got here (from DRJ):


Public Sub ProtectAll()
Const PWORD As String = "MySecretWord"
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
wsSheet.Protect Password:=PWORD, DrawingObjects:=False, _
Contents:=True, Scenarios:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True, UserInterfaceOnly:=True, _
AllowFormattingCells:=True, AllowFormattingRows:=True
Next wsSheet
End Sub


I want to also allow inserting rows. I attempted AllowInsertingRows:=True and InsertingRows:=False, but neither works. Could you a lend a hand with this?

Is there a more convenient place to find these VBA expressions than in Microsoft's Online Help? I find it extremely difficult to find anything there.

Thanks for your help

Jacob Hilderbrand
02-12-2005, 04:15 PM
AllowInsertingRows:=True
That is the correct line. I just use the Macro Recorder and make the changes manually then see what the code is.

K. Georgiadis
02-12-2005, 06:30 PM
Thank you, I think I may have forgotten to separate the last two statements with a comma.

Now, I'm getting a phenomenon which will be a little disconcerting to a first time user of my workbook. Having protected the workbook with the above code, I also have the following code in "This Workbook" that is supposed to unprotect the workbook, refresh the Pivot Table and then re-protect:



Private Sub Workbook_Open()
Dim ws As Worksheet
Dim pt As PivotTable
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="mySecretWord"
For Each pt In ws.PivotTables
pt.RefreshTable
Next
ws.Protect DrawingObjects:=False, _
Contents:=True, Scenarios:=True, _
AllowUsingPivotTables:=True, UserInterfaceOnly:=True, _
AllowFormattingCells:=True, AllowFormattingRows:=True
Password:="MySecretWord"
Next
UserForm1.Show
Sheets(?Instructions?).Select
Range(?A1?).Select
End Sub


When the workbook opens, there is a dizzying flurry of activity, as though Excel is rapidly cycling through all the worksheets. Not damaging but, as I said, a little disconcerting. Is it the combination of these two codes that cause this to happen and how might I prevent it?

Thank you

Jacob Hilderbrand
02-12-2005, 06:32 PM
Put this at the start of your macro.

Application.ScreenUpdating = False

K. Georgiadis
02-12-2005, 06:46 PM
Terrific!

Jacob Hilderbrand
02-12-2005, 06:52 PM
Glad to help. :beerchug:

Take Care

K. Georgiadis
02-12-2005, 06:58 PM
I am on the East coast of the US and noticed that your message is stamped 1:55 AM, 5 hours ahead of me. Is this website floating in the middle of the Atlantic Ocean?
:)

Jacob Hilderbrand
02-12-2005, 07:06 PM
You have your time zone set to GMT. Click on User CP then Edit Options. Set your time zone to Eastern and all the times will be correct for you.