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

spydey

Active Member
Joined
Sep 19, 2017
Messages
314
Office Version
  1. 2013
Platform
  1. Windows
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:

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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
How about moving 'ws.Unprotect' just below 'If Not Intersect ....'?
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,996
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top