VBA for worksheet change (add row) event (I think)
 

2003/2007


When I applied worksheet protection, I "permitted" the User to add rows to the sheet.

What VBA can I use to remove protection from all cells in the newly added row
so that the user may fill the cells with data, without permitting the User to change
data in all the previously existing cells?

I see two issues:
(1) How do I "select" just the newly added cells within the UsedRange of the protected sheet?
(2) Formatting those cells is not my concern as I know how to do that.

TIA EagleOne

VBA for worksheet change (add row) event (I think)
 

Partial success.

How can I limit the change event below to just add row change events?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myRange As Range
ActiveSheet.Unprotect Password:="xxxx"
Set myRange = Intersect(ActiveSheet.UsedRange, Selection)
Selection.Locked = False
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="xxxx", DrawingObjects:=False, Contents:=True, _
Scenarios:=False, AllowFormattingCells:=True, AllowFormattingRows:=True, _
AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
End Sub


wrote:

2003/2007


When I applied worksheet protection, I "permitted" the User to add rows to the sheet.

What VBA can I use to remove protection from all cells in the newly added row
so that the user may fill the cells with data, without permitting the User to change
data in all the previously existing cells?

I see two issues:
(1) How do I "select" just the newly added cells within the UsedRange of the protected sheet?
(2) Formatting those cells is not my concern as I know how to do that.

TIA EagleOne