You are here:

Excel/PROTECTED WORKSHEETS & ALLOWABLE ACTIONS

Advertisement


Question
QUESTION: Hi Tom,

I am using Excel 2010.  I have all sheets protected in my workbook as this file is a form that only needs certain cells to be updated by the users.   I currently have a macro set up that will unprotect all sheets run spell check and then protect the sheets again.  The problem I am running into is the allowable action of "Edit objects" doesn't get checked when the macro re-protects the sheet.  It appears the default allowable action selected by the macro is “Select unlocked cells”.  Below is my code that I have in macro now.  Is there anything I can add to it that will make the sheets re-protect with the additional option of "Edit Objects" allowable action?  My final questions regarding my macro is; I’m also looking to exclude Sheet5 from the spell check that the macro runs.  Is there a way to exclude a specific sheet within the workbook from the spell check by adding something to my code below?
Sub spell_check_allsheets()
 Dim WS As Worksheet
     Application.ScreenUpdating = False
     For Each WS In ActiveWorkbook.Worksheets
     WS.Select
     ActiveSheet.Unprotect Password:="Password"
     Cells.CheckSpelling SpellLang:=1033
     ActiveSheet.Protect Password:="Password"
       Next WS
     Application.ScreenUpdating = True
     Worksheets("Sheet1").Select
End Sub

Thanks in advance for any guidance you can provide.

Regards,
Will

ANSWER: Will,

Sub spell_check_allsheets()
Dim WS As Worksheet
Application.ScreenUpdating = False
For Each WS In ActiveWorkbook.Worksheets
WS.Select
 if WS.Name <> "Sheet5" Then
   ActiveSheet.Unprotect Password:="Password"
   Cells.CheckSpelling SpellLang:=1033
   ActiveSheet.Protect Password:="Password"
 End if
Next WS
Application.ScreenUpdating = True
Worksheets("Sheet1").Select
End Sub

The protect method has many arguments:

expression.Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)

the easiest way to see what command you need is to turn on the macro recorder and then protect the sheet with the options that you want.  Now if you use all the default settings, then you will probably only record something like:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

so your probably need to select at least on non-default setting such as AllowFormattingColumns as an example. then it should record like this:

ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
       False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
       AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
       :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
       AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
       AllowUsingPivotTables:=True

go to help on the protect method for the worksheet and it will explain each of the options and what the defaults are.


as for objects, the parameter you want is DrawingObjects.  The help says that protects shapes and shapes are objects on the worksheet.

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Thank you Very Much Tom!  You’ve been a very big help.  You’re advice helped me with what I was looking for.  I do have a follow up question I hope you're able to answer.  

Regarding the code you guided me to in order to skip sheet5 as I requested.  I want to hide sheet5 while leaving the other sheets visable.  However, when I hide sheet5 and run my macro (code is listed below) it generates a “Run time error ‘1004’ Method  ‘Select’ of object’_worksheet’ failed.  When I unhide sheet5 and run the macro it runs fine.  

Is there something I can put in my code listed below that will allow me hide sheet5, yet still run my macro as I have it?


Sub spell_check_allsheets()
Dim WS As Worksheet
Application.ScreenUpdating = False
For Each WS In ActiveWorkbook.Worksheets
WS.Select
If WS.Name <> "sheet5" Then
  ActiveSheet.Unprotect Password:="password"
  Cells.CheckSpelling SpellLang:=1033
  ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
       False, Password:="password"
         End If
Next WS
Application.ScreenUpdating = True
Worksheets("sheet1").Select
End Sub


Thanks again for the help.

Regards,

Will

Answer
Will,

If the sheet is hidden, then you can't select it.  so just move that inside the IF statement like this:


Sub spell_check_allsheets()
Dim WS As Worksheet
Application.ScreenUpdating = False
For Each WS In ActiveWorkbook.Worksheets

If lcase(WS.Name) <> lcase("sheet5") Then
  WS.Select  '< moved inside the IF test
  ActiveSheet.Unprotect Password:="password"
  Cells.CheckSpelling SpellLang:=1033
  ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
       False, Password:="password"
End If
Next WS
Application.ScreenUpdating = True
Worksheets("sheet1").Select
End Sub

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.