VBA to acsertain if last w/s change was "insert Row"

[EagleOne]

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

 

[Gary''s Student]

Take a look at:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Rows.Count = 1 Then
If Target.Columns.Count = Columns.Count Then
MsgBox (" a row just might have been added")
End If
End If
End Sub

It is possible to keep track of the number of rows in ActiveSheet.UsedRange
by using a static variable (declare it just before the sub rather than after
the sub).

However, if the user inserts a cell and pushes the others down, then the
number of rows in UsedRange might grow even though a new row had not been
added.

Consider using all three tests.

 

[Bob Phillips]

See if this works for you

Private NumRows As Long

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

If Me.UsedRange.Rows.Count > NumRows Then

'your code

NumRows = Me.UsedRange.Rows.Count
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
NumRows = Me.UsedRange.Rows.Count
End Sub

 

[EagleOne]

The following seems to work. That said, is there a better/smarter way?

IN THE GENERAL MODULE: (I had this in the worksheet module originally)

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


IN THE WORKSHEET MODULE:

Private Sub Worksheet_Change(ByVal Target As Range)

If Sheets("Trial Balance Current").Rows.Count > X 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

End Sub

 

[EagleOne]

Gary's Student.

Finally, I woke up the giants.

Much appreciated!! Please look at my code submitted after I asked the question.
For my learning process what are its problems if any?

 

[EagleOne]

Glad to see your response Bob!

Much appreciated!! Please look at my code submitted after I asked the question.
For my learning process what are its problems if any?

 

[Gary''s Student]

As you have already noticed, variables are usually NOT preserved. However,
if you Dim the variable above the Sub statement, they will be preserved.

For example:

Dim OldRowCount As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(OldRowCount) Then
OldRowCount = ActiveSheet.UsedRange.Rows.Count
Else
MsgBox ("there are " & ActiveSheet.UsedRange.Rows.Count & " rows")
MsgBox ("there were " & OldRowCount & " rows")
OldRowCount = ActiveSheet.UsedRange.Rows.Count
End If
End Sub

1. notice the Sub follows the Dim (opposite of the usual case)
2. we don't need a function, just a variable
3. because OldRowCount is a memory variable, it won't have a value the very
first time the sub is called, so I test for this.
4. you could also have Dim'ed the variable by itself in a standard module

 

[EagleOne]

You do an excellent job!

Thanks for your time and knowledge.

 

[Gary''s Student]

Thanks for the feedback.