ws.Protect causing code not to run .... should be simple ?????

[spydey]

Yesterday, both @igold & @Akuini were very generous in assisting me with figuring out how to code something. We were able to figure it out (thank you again!!).

Here is the thread: Push Down Rows

However, now I am up against a different issue, but involving the same piece of code.

The code basically does this: I have a table with is 11 columns wide. When I input data in the row after the table's last row, the table is then re-sized and that row is now included in the table as the table's new last row. It also inserts two rows (really a range 11 columns wide, not full rows) after the newly re-sized table. This keeps any data below the original table at the same "distance" from the table, even after resizing.

However, I need to have the worksheet protected, due to some in-cell formulas that I don't wanted being changed by accident.

Some cells I have unlocked so that I can input data as needed. Columns 3 - 7 of the table are unlocked for input while the sheet is protected. Also, columns 3 - 7 of the row after the table's last row, are unlocked for data input. By having the row after table's last row unlocked, I can input data and then the table is resized and that row now becomes part of the table.

What I need to happen is once the table is resized and the two rows are added after the table's new last row, I need the row after the table's new last row to have columns 3 - 7 unlocked so that I can have data input in them and do the above once again.

I thought it to be simple and it seemed to be for the most part.

Here is the whole code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim wb As Workbook
Dim ws As Worksheet
Dim SummaryHeader, Summary As Range


Set wb = ThisWorkbook
Set ws = wb.Worksheets(1)
Set SummaryHeader = Range("SummaryHeader")
Set Summary = Range("Summary")


With ws.ListObjects("tblDelays")


If Not Intersect(Target, .ListRows(.Range.Rows.Count - 1).Range) Is Nothing Then
    
    Dim r As Range
    
    Set r = .Range.Offset(.Range.Rows.Count).Resize(2, 1)
    
    ws.Unprotect
    
    With Range("tblDelays")
        .Rows.AutoFit
        With .Borders(xlEdgeTop)
                .LineStyle = xlDouble
                .ColorIndex = xlAutomatic
                .TintAndShade = 0
                .Weight = xlThick
        End With
        With .Borders(xlEdgeBottom)
                .LineStyle = xlDouble
                .ColorIndex = xlAutomatic
                .Weight = xlThick
        End With
        With .Borders(xlInsideVertical)
                .LineStyle = xlContinuous
                .ColorIndex = xlAutomatic
                .TintAndShade = 0
                .Weight = xlThin
        End With
        With .Borders(xlInsideHorizontal)
                .LineStyle = xlContinuous
                .ColorIndex = xlAutomatic
                .TintAndShade = 0
                .Weight = xlThin
        End With
    
    End With
    
    If WorksheetFunction.CountA(r) > 0 Then
    Application.EnableEvents = False
        r.Range(Cells(2, 1), Cells(2, 11)).Resize(WorksheetFunction.CountA(r)).Insert xlShiftDown
[B]        r.Range(Cells(1, 3), Cells(1, 9)).Locked = False[/B]
        r.RowHeight = 16.5
        SummaryHeader.Rows.AutoFit
        Summary.Rows.AutoFit
    Application.EnableEvents = True
    
    End If
End If


End With


ws.Protect


End Sub

I added the code in bold to have the row after the newly resized table have the specific cells unlocked.

As long as I have the worksheet unprotected, everything works correctly/as desired.

However, once I have the worksheet protected, then nothing works, not even have the table resized.

Even if I unprotect the sheet prior to running the code, and then re-protect it (all via code) it still doesn't work for some reason.

It is like right there but I can't seem to see it ..... I am thinking it has to do with the timing of the intersect and or maybe not using the "r" correctly in the additional line of code I added.

"r" is set at the beginning as a Range Offset Rows Count with resize. I am thinking that the resize might be interfering for some reason .... but still, that doesn't explain why it works when the sheet is unprotected, and won't work when it is unprotected via code and then re-protected after the changes are made ......

Any thought, insight, ideas, etc that anyone can provide as to why the code isn't running correctly, I would greatly appreciate it!!

-Spydey

 

[Akuini]

How about moving 'ws.Unprotect' just below 'If Not Intersect ....'?

 

[spydey]

How about moving 'ws.Unprotect' just below 'If Not Intersect ....'?

Worked like a charm!!

Thanks @Akuini

-Spydey