PW Protected Workbook Sturcture Issue

[r0bism123]

Hello,

I'm having an issue that I hoping someone can help me out. I've posted on this issue before but I cannot seem to make it work.

Here is the URL to the other string: https://www.mrexcel.com/forum/excel...ect-workbook-structure-issue.html#post5109213

I have a workbook that has about 60ish password protected sheets. One of these sheets controls the names of the different tabs by executing the code below. In short, I change the name from "Suite 1" to "Suite 2" and the corresponding tab also changes in the proper order. The code below works perfectly fine when the workbook structure is not protected, however, when the workbook structure is protected, it stops working and will not change the name of the tab.

I am hoping there is a workaround that will unprotect the workbook structure, execute the code below, then password protect the sheet and password protect the workbook structure once again.

Just so am I clear as possible, I took of screen shot of the "workbook structure" menus.

Any help is greatly appreciated!

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Dim ans As Long
On Error GoTo M
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Target.Row > 15 Then
            ans = Target.Row
            If Target.Value = "Vacant" Or Target.Value = "vacant" Then
                Sheets(ans + 1).Tab.Color = RGB(255, 76, 76)
                Sheets(ans + 1).Name = Sheets(ans + 1).Cells(5, 3).Value
            Else
                Sheets(ans + 1).Tab.Color = RGB(255, 248, 66)
                Sheets(ans + 1).Name = Target.Value
             End If
        End If
    End If
   
Exit Sub
M:
MsgBox "Error: Duplicate Tenant Name"
End Sub

 

[r0bism123]

You can disregard this thread. I figured it out! Thanks!