Unprotect Worksheet

Question

I am using the following code in the Worksheet_Change. However, I am using other macros within the same Worksheet as well. When I run the macros (see sample below), because the Worksheet_Change is protecting the Worksheet, the macros will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False

ActiveSheet.Unprotect ("1") ''''''''''''''''''Re-Activate

With Target
If .MergeCells And .WrapText Then
Set c = Target.cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.cells
         MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
     ma.MergeCells = False
      c.ColumnWidth = MrgeWdth
       c.EntireRow.AutoFit
        NewRwHt = c.RowHeight
       c.ColumnWidth = cWdth
     ma.MergeCells = True
    ma.RowHeight = NewRwHt
   cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If

ActiveSheet.Protect ("1") ''''''''''''''''''Re-Activate

End With
    Application.ScreenUpdating = True

ErrorHandler:
Application.EnableEvents = True
End Sub

Sub Office_Counter_E()
Application.ScreenUpdating = False
    Sheet17.Unprotect ("1")
    Sheet17.Range("AC5").Value = 1
    Sheet17.Range("AD5").Value = ""
    Sheet17.Range("AE5").Value = ""
    Sheet17.Range("AF5").Value = ""
    Sheet17.Range("AG5").Value = ""
    Sheet17.Protect ("1")
Application.ScreenUpdating = True
End Sub

After researching online, I found the following code that appears to work. Is this code trustworthy? ```Sheet17.Protect Password:="1", UserInterFaceOnly:=True``` — BrentJohn, Mar 04 '23 at 11:03
[Yes](https://stackoverflow.com/a/126032/11683), with a [caveat](https://stackoverflow.com/a/14162717/11683). — GSerg, Mar 04 '23 at 12:21
UIFO is not always working. https://stackoverflow.com/questions/24999377/userinterfaceonly-true-doesnt-seem-to-allow-vba-changes-to-conditional-formatt And there are more when I remember right. So be sure to test the things you do work on UIFO. And be aware that other code can work on these worksheets. But I use it my self and with critical sheets I unprotect and protect (But since Excel 2013 this is really slow, because of some very stupid action from MS) — Red Hare, Mar 04 '23 at 15:02

score 0 · Answer 1 · answered Mar 04 '23 at 12:13

If you want macros to be able to make changes while your sheet is protected, you can use Workbook_Open event to set it the way you mentioned in your comment, yes. UserInterfaceOnly only makes it so the macros have freedom while your user doesn't, perfectly safe/trustworthy (unlike your password :p)

Private Sub Workbook_Open()

Dim Sh As Worksheet

    For Each Sh In Worksheets
        Sh.Protect Password:="1", UserInterFaceOnly:=True
    Next
    
End Sub

Or only for Sheet17:

Private Sub Workbook_Open()

    Sheet17.Protect Password:="1",  UserInterFaceOnly:=True
    
End Sub

If you do it from Workbook_Open, you won't have to unprotect/protect anywhere else for the sake of your other macros.

Thank you much for confirming. As well as the VERY helpful hint about placing the code in the Workbook_Open. — BrentJohn, Mar 04 '23 at 12:37

Unprotect Worksheet

1 Answers1