2003/2007

What VBA can I use to trigger additional code if the last action was "Insert Row?"

OR

How to inhibit execution of Worksheet_Change() unless the last w/s change was insert row.

NOTE: I wish that the code be in the worksheet object module (so as to use worksheet events as the
trigger)

Notice the IF clause below. It is this code line that I need clarified. How do I capture
rows.count when the w/s is activated then measure rows.count after row-insertion. If I can, then
the code below will work fine. The issue is how to extend the life of the variable "OrigRows" from
Worksheet_Activate event so that I compare to the current rows.count after worksheet_Change event.

Is there a better way? i.e., VBA-read the last action (via ReDo list?) to trigger the If Clause
below?

'Private Sub Worksheet_Change(ByVal Target As Range)

If Sheets("Trial Balance Current").Rows.Count OrigRows Then
Dim myRange As Range
ActiveSheet.Unprotect Password:="xxxxx"
Set myRange = Intersect(ActiveSheet.UsedRange, Selection)
Selection.Locked = False
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="xxxxx", DrawingObjects:=False, Contents:=True, _
Scenarios:=False, AllowFormattingCells:=True, AllowFormattingRows:=True, _
AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
End If

***********

The code I could not get to work effectively (preserve the life of the variable).


Function OrigRows() As Long
OrigRows = Sheets("Trial Balance Current").UsedRange.Rows.Count
End Function

Private Sub Worksheet_Activate()

' How do I preseve OrigRows in the function above to use in the code above?

End Sub


Any thoughts appreciated, EagleOne