Code to UNPROTECT then PROTECT my WORKBOOK when Macro runs - Can someone show me please???

[Barryoffshore]

Okay, I have this code and it works very well - but it only works if my WORKBOOK is unprotected. I know (thanks to this forum and ALL the GREAT people here) how to Unprotect then Protect an ActiveSheet, but I can't figure out how to Unprotect then Protect my workbook when the macro runs... Can someone please show me what I need to do?

Thanks!!! Bear

Sub BLM_RENAME_SHEET()
Dim WS As Worksheet
Application.ScreenUpdating = False
For Each WS In ActiveWindow.SelectedSheets
WS.Activate
A1 = WS.Range("C2").Value
If A1 <> "" Then
If InStr(C2, " - ") Then
s = Split(Trim(E8), " - ")
WS.Name = s(0)
Else
WS.Name = WS.Range("C2").Value
End If
End If
Next
End Sub

 

[Barryoffshore]

I have to give a HUGE SHOUT OUT to CharlesH and Michael M....... THANK YOU! THANK YOU! THANK YOU!

Y'all gave me the advice and the tools to learn, which is an amazing thing!

The code that finally worked is:

Sub BLM_RENAME_SHEET()
Dim WS As Worksheet
Application.ScreenUpdating = False
ActiveWorkbook.Unprotect ("blm_rental")
For Each WS In ActiveWindow.SelectedSheets
    WS.Activate
        A1 = WS.Range("C2").Value
            If A1 <> "" Then
            If InStr(C2, " - ") Then
            s = Split(Trim(C2), " - ")
                WS.Name = s(0)
                Else
            WS.Name = WS.Range("C2").Value
        End If
    End If
ActiveWorkbook.Protect ("blm_rental")
Next
End Sub

 

[Michael M]

Excellent Bear....another giant leap forward...