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

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.
--
Gary''s Student - gsnu2007L


" wrote:

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.

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?


Gary''s Student wrote:

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.

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
--
Gary''s Student - gsnu200828


" wrote:

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?


Gary''s Student wrote:

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.

You do an excellent job!

Thanks for your time and knowledge.

Gary''s Student wrote:

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

Thanks for the feedback.
--
Gary''s Student - gsnu200828


" wrote:

You do an excellent job!

Thanks for your time and knowledge.

Gary''s Student wrote:

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

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


--
__________________________________
HTH

Bob

wrote in message
...
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

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?


"Bob Phillips" wrote:

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

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

wrote:

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