Excel vba activesheet.protect/unprotect

A worksheet can be protected in such a way that only cells that need to be changed can be changed. Protecting a worksheet involves two different settings or attributes: worksheet protection and cell locking. When a worksheet is protected, all cells that are locked become uneditable and those cells that are not locked remain editable. To lock or unlock a cell, select the cell or cells and choose the menu command Format->Cells (in Excel 2007 press ALT, O, E), navigate to the Protection tab, and check the Locked check box on to lock the cell or cells or off to unlock. Note that on all new worksheets all cells default to being locked.

To protect a worksheet, choose the menu command Tools->Protection->Protect Sheet (in Excel 2007 and later navigate to the Review tab and click Protect Sheet in the Changes group or press ALT, T, P, P). Click OK to lock the worksheet without a password. To protect the worksheet with a password in order to prevent others from unprotecting it, enter a password. Other settings on the Protect Sheet dialog provide the ability to prevent or allow other functions such as selecting locked cells, sorting, filtering, deleting and inserting rows and columns, formatting cells, etc. Note that most of these settings are only available in Excel 2002 and later.

To unprotect a worksheet, choose the menu command Tools->Protection->Unprotect Sheet (in Excel 2007 and later navigate to the Review tab and click Unprotect Sheet in the Changes group). If the worksheet is password protect a password will have to be entered to complete the unprotection.

To prevent the user from viewing formulas while the worksheet is protected, before protecting it select the cells for which the formulas need to be hidden, choose the menu command Format->Cells (in Excel 2007 and later navigate to the Home tab and select Format Cells from the Format menu in the Cells group or press ALT, O, E), navigate to the Protection tab, and check on the Locked and Hidden check boxes.

Kevin

Don't you have the Excel sheet protected? Usually this is used on a protected sheet to unprotect it so that you can make same changes and at the end of the changes, protect the sheet again. Are you sure you don't have nothing on you sheet that is protected?

Since Excel 2000 there has been an interesting additional attribute that allows the worksheet to be protected while allowing VBA to still work with the worksheet.

When a sheet is protected, Excel not only prevents the user from changing locked worksheet elements, it also prevents VBA from changing them. This is circumvented by unprotecting the sheet, making the changes, and then protecting the worksheet again as illustrated below.

   Sheets("Sheet1").Unprotect Password:="password"
   ...
   Sheets("Sheet1").Protect Password:="password"

An alternative and more efficient technique is to use the UserInterfaceOnly option on the Protect method. This option protects the sheet from manual changes the same as without the option, but it allows VBA access to the sheet's elements as if the sheet was unprotected. The only drawback is that the option setting cannot be set from the Excel application user interface and it is not saved with the workbook. This means that it has to be reset every time the workbook opens. This is accomplished by placing the code to set the option in the Workbook_Open or similar routine.

Private Sub Workbook_Open()

   Sheets("Sheet1").Protect Password:="password", UserInterfaceOnly:=True
   Sheets("Sheet2").Protect Password:="password", UserInterfaceOnly:=True

End Sub

Note that protection does not need to be turned off to make the change. The above code can be placed anywhere in the project as long as the UserInterfaceOnly setting is adjusted before the first attempt is made to make change a sheet element.

The UserInterfaceOnly option is available starting with Excel 2000.

Note that even with UserInterfaceOnly set to True, certain operations cannot be performed such as

- adding drawing objects and charts to the worksheet,
- turning AutoFilter on and off, and
- adding data validation to cells.

When these operations must be perfomed on a worksheet, the worksheet must be unprotected and protected again. Any operation attempted on a worksheet protected with the UserInterfaceOnly option that is not allowed will generate a 1004 error.

Kevin

thanks Kevin and jppinto

Jppinto,
thats exactly what I am thinking but I am not realy an excel guru so not sure if its protected but the idea is that if I can see or anything without a pass then its not protected now I can tell exactly where the records have been written but sure how to check if its protected
Idea is that it could just be a preventive active
Can you please confirm how can I tell if let say a tab is protected?

In short, a worksheet is protected if any of the worksheet properties ProtectContents, ProtectDrawingObjects, or ProtectScenarios are True. Typically, the ProtectContents property is the most commonly used as it defines whether or not locked cells are protected. From the Excel application user interface a worksheet cannot be protected unless the Protect Contents option is set on.

There are five protection related properties and objects in a worksheet object:

ProtectContents - True if locked cell contents (values and formulas) are protected, False otherwise. When a locked cell is protected it cannot be edited or changed with any commands. Cells are locked and unlocked from the Protection tab on the Format Cells dialog. The easiest method for determining if a worksheet is locked is to check the worksheet's ProtectContents property - if True then the worksheet is locked or protected.

ProtectDrawingObjects - True if all locked drawing objects are protected, False otherwise. When a locked drawing object is protected it cannot be edited or changed with any commands. Drawing objects are locked and unlocked from the Protection tab on the Format AutoShape and Format Picture dialogs.

ProtectScenarios - True if all locked scenarios are protected, False otherwise. When a locked scenario is protected it cannot be edited or changed with any commands. Scenarios are locked and unlocked by setting the "Prevent changes" checkbox on the Edit Scenario dialog accessable from the Scenario Manager dialog.

ProtectionMode - True if user interface only protection is enabled, False otherwise. This setting does not affect the behavior of the worksheet, it only affects whether or not VBA code is affected by the various protection modes. When True VBA is able to make modifications to any protected objects as if they were not protected.

Protection - A set of properties defining what other worksheet features are protected (not allowed). These are AllowDeletingColumns, AllowDeletingRows, AllowEditRanges, AllowFiltering, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingHyperlinks, AllowInsertingRows, AllowSorting, and AllowUsingPivotTables. AllowEditRanges is a collection of AllowEditRange objects that contain various other properties describing who can edit what ranges. Note that all properties in the Protection object are only available in Excel 2002 and later versions.

Additional Notes

Executing the Unprotect command sets the ProtectContents, ProtectDrawingObjects, and ProtectScenarios to False, it does not set the ProtectionMode to False. When a subsequent Protect command is executed without specifying the UserInterfaceOnly parameter the ProtectionMode property is again left unchanged. The net result of this behavior is that the worksheet can be unprotected for the user and then reprotected again without the need to restore the ProtectionMode property. However, the ProtectionMode property is not saved with the workbook and is always reset to False when a workbook is opened. Therefore it must be maintained when a workbook is opened if VBA code relies on it being set to True.

While it is not possible to protect only drawing objects and/or scenarios while not protecting cells using the application user interface, it is possible using VBA:

   TargetWorksheet.Protect Contents:=False, DrawingObjects:=True, Scenarios:=False

The Excel help states that the default value for the Protect command's Contents parameter is True - this is not true when setting either the DrawingObjects or Scenarios to False. When doing so and not specifying the Contents parameter, the ProtectContents property is set to False.

The ProtectionMode property that defines whether or not VBA has unrestricted access to the worksheet can only be set by using the Protect method's UserInterfaceOnly parameter. It has to be set everytime a workbook is opened as the property is not saved with the workbook. To set the property without affecting the other protection settings the Protect method must be executed by specifying all parameters as illustrated in the two examples below. This technique works because the Protect method is effective even if the worksheet is already protected.

In Excel 2002 and later use:

   TargetSheet.Protect _
      DrawingObjects:=TargetSheet.ProtectDrawingObjects, _
      Contents:=TargetSheet.ProtectContents, _
      Scenarios:=TargetSheet.ProtectScenarios, _
      UserInterfaceOnly:=True, _
      AllowFormattingCells:=TargetSheet.Protection.AllowFormattingCells, _
      AllowFormattingColumns:=TargetSheet.Protection.AllowFormattingColumns, _
      AllowFormattingRows:=TargetSheet.Protection.AllowFormattingRows, _
      AllowInsertingColumns:=TargetSheet.Protection.AllowInsertingColumns, _
      AllowInsertingRows:=TargetSheet.Protection.AllowInsertingRows, _
      AllowInsertingHyperlinks:=TargetSheet.Protection.AllowInsertingHyperlinks, _
      AllowDeletingColumns:=TargetSheet.Protection.AllowDeletingColumns, _
      AllowDeletingRows:=TargetSheet.Protection.AllowDeletingRows, _
      AllowSorting:=TargetSheet.Protection.AllowSorting, _
      AllowFiltering:=TargetSheet.Protection.AllowFiltering, _
      AllowUsingPivotTables:=TargetSheet.Protection.AllowUsingPivotTables

In Excel 2000 and earlier use:

   TargetSheet.Protect _
      DrawingObjects:=TargetSheet.ProtectDrawingObjects, _
      Contents:=TargetSheet.ProtectContents, _
      Scenarios:=TargetSheet.ProtectScenarios, _
      UserInterfaceOnly:=True

Note that while Excel does not reset the properties in the Protection object when unprotecting a worksheet, it does reset the three main properties ProtectContents, ProtectDrawingObjects, and ProtectScenarios which means that if the worksheet is ever unprotected using the Unprotect method, those three properties must be saved before hand and used when the worksheet is again protected.

Kevin

"In short"? :)

I'm not in a short mood today.

I've noticed. There is such a thing as too much information, you know. ;)

Yep. Been there a few times. Sometimes I go long, sometimes I go short.

I am not sure of I am write but problem is that I am unable to find any VBA code in the sheet which protects it.

Thanks for help guys.
Kevin,
I think rorya was right. It actually was a little too much for me as I was looking for a quick fix. :)
but in the end you were right. I could just unprotect the sheet from UI and VBA code is just doing that to be able to do work on the sheet.

Thanks again