View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
[email protected] EagleOne@discussions.microsoft.com is offline
external usenet poster
 
Posts: 391
Default How to extend the life of a variable from one Private Sub routine to another?

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