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:
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
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