Protect Workbook Structure Issue

[r0bism123]

So, I'm having an issue and from the research I've done, I don't think there is solution but I will pose the question to the experts here!

I have a workbook that I have password protected each sheet. I also have the code below integrated on one sheet that triggers a name change and color change on the pw protected tab. However, when I protect the workbook structure (which I need to do so the order the tabs cannot be changed), this prevents me from executing the code because the workbook structure is protected.

I am wondering if there is a work-around to unprotect the workbook structure, execute the code below, then password protect the sheet and password protect the workbook structure once again. Thoughts?

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

 

[RoryA]

That is exactly how you would do it - what happened when you tried?

 

[r0bism123]

Hi RoryA,

The code activates only when the workbook structure is unlocked. When I lock the workbook structure with a PW, the tabs no longer change their name or color and it gives me an error message "Error: Duplicate Tenant Name".

Just clarify, the worksheets lock/unlock perfectly without the workbook structure locked. It stops when I PW protect the workbook structure (file > protect workbook > protect workbook structure). Hope that makes sense...

 

[RoryA]

I meant that your "workaround" is exactly how you would do it.

 

[r0bism123]

Why do you think it not working? It pops up with that error message because it cannot change the tab name. The only thing I can do is leave the workbook structure unlocked and hope that someone doesn't change the order of the tabs. I'm at a loss...

 

[Fluff]

Record a macro of you unlocking & then relocking the workbook.
That will give you the necessary code that you can add to your existing macro

 

[r0bism123]

Good idea. Thanks. I'll give that a try.

 

[r0bism123]

I found a another source that spoke about a solution and they provided the code:

ActiveWorkbook.Protect Password:="test", Structure:=True, Windows:=True

I need the workbook to first unlock, perform the code below and then lock at the end of the sub.

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

I've tried a few times to make this work but my VBA is weak sauce lol. Any help here is really appreciated!

 

[RoryA]

At the start you need:

ActiveWorkbook.unProtect Password:="test"

then add your other line at the end.