How to extend the life of a variable from one Private Sub routine to another?

[EagleOne]

2003/2007

How can I keep the Var "OrigRows" available from
Private Sub Worksheet_Activate() thru to
Sub Worksheet_Change(ByVal Target As Range) see below.

If not, how can I best execute the "If" clause only when the
worksheet change event in an add-to-rows?

********************

Private Sub Worksheet_Activate()

OrigRows = Sheets("Trial Balance Current").UsedRange.Rows.Count

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

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

***************

TIA EagleOne

 

[smartin]

2003/2007

How can I keep the Var "OrigRows" available from
Private Sub Worksheet_Activate() thru to
Sub Worksheet_Change(ByVal Target As Range) see below.

If not, how can I best execute the "If" clause only when the
worksheet change event in an add-to-rows?

********************

Private Sub Worksheet_Activate()

OrigRows = Sheets("Trial Balance Current").UsedRange.Rows.Count

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

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

***************

TIA EagleOne

I think you might want to declare OrigRows as a global. Also, if you
expect this to remain static through the life of the workbook you will
need to make sure it is not overwritten on each _Activate event. This
can be done with some code in said procedure with a global or static
variable to hold the state of whether OrigRows has been updated.

 

[EagleOne]

I think i got your thought, thanks!

I think you might want to declare OrigRows as a global. Also, if you
expect this to remain static through the life of the workbook you will
need to make sure it is not overwritten on each _Activate event. This
can be done with some code in said procedure with a global or static
variable to hold the state of whether OrigRows has been updated.