How do I avoid run-time error when a worksheet is protected in MS-Excel?

by

If I understand the question correctly, you will be the one protecting the sheet. If that is the case, you can use the following VBA:

myWorksheet.Protect contents:=True, userinterfaceonly:=True

The key part here is “userinterfaceonly:=true”. When a worksheet is protected with this flag set, VBA macros are still allowed to make changes.

Place this code into the WorkBook_Activate event to automatically protect the workbook and set the flag whenever it is activated.

Edit: Thanks to Lance Roberts for his recommendation to use Workbook_Activate instead of Workbook_Open.

Edit: Since the above didn’t seem to work, you may have to wrap the failing portion of your VBA code with unprotect/protect commands. If you do that, I would also wrap the entire macro with an error handler, so that the sheet is not left unprotected after an error:

Sub MyMacro
    On Error Goto HandleError

    ...

    myWorksheet.unprotect
    With ModifyCell.Validation
        ...
    End With
    myWorksheet.protect contents:=True, userinterfaceonly:=True

    ...

Goto SkipErrorHandler
HandleError:
    myWorksheet.protect contents:=True, userinterfaceonly:=True
    ... some code to present the error message to the user
SkipErrorHandler:
End Sub

Edit: Have a look at this thread at PCreview. They went through much the same steps, and came to the same conclusion. At least you’re not alone!

More Related Contents:

  1. using for loop for vba
  2. Simple VBA code to open cells one after the other
  3. Open folder in new instance
  4. Refer to sheet using codename
  5. VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds
  6. Assign code to a button created dynamically
  7. Last not empty cell (column) in the given row; Excel VBA
  8. HTML Text with tags to formatted text in an Excel cell
  9. Split comma separated entries to new rows [closed]
  10. Get list of Excel files in a folder using VBA [duplicate]
  11. Select first empty cell in column F starting from row 1. (without using offset )
  12. Excel VBA – QueryTable AfterRefresh function not being called after Refresh completes
  13. VBA.Find and adding a value
  14. excel vba- extract text between 2 characters
  15. Round function in Excel, worksheet function vs VBA
  16. Excel VBA Userform – Execute Sub when something changes
  17. Count the number of rows in another sheet [duplicate]
  18. Getting Excel to refresh data on sheet from within VBA
  19. VBA Password Input with Cancel Function
  20. Finding first blank row, then writing to it
  21. How can an Excel Add-In respond to events in any worksheet?
  22. Fastest way to check if two ranges are equal in excel vba [duplicate]
  23. Remove double quotation from write statement in vba
  24. Meaning of .Cells(.Rows.Count,”A”).End(xlUp).row
  25. Missing VBA compiler message for wrong method name
  26. How do I loop an excel 2010 table by using his name & column reference?
  27. Is there a way to make UDF that gives a description like the native Excel functions have when user clicks the Fx button?
  28. How to improve the speed of VBA macro code?
  29. Delete Hidden/Invisible Rows after Autofilter Excel VBA
  30. How to copy only a single worksheet to another workbook using vba

Leave a Comment