Help with PROPERTY GET

[EagleOne]

2003/2007

Need to have Var "OrigRows" established in a Worksheet_Activate event.

Then, once established, I would like to have "OrigRows" available to a second
Private Sub Worksheet_Change procedure as below.

I understand that I cannot declare a Global or Public variable in an object

I have never used a Propery Get. How can I weave the code below together?



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

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

End Sub

TIA EagleOne

 

[EagleOne]

Leith,

I realize that the code next will not work.

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

That said, I would like
(1) the variable "OrigRows" to be established when the worksheet is activated and
(2) be available to Private Sub Worksheet_Change.

How can I do this to make the following work? (I need OrigRows to have a value)


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

End Sub


How can I get

 

[got.sp4m]

Actually I don't think there is any need to use properties for this
one..

See if this does what you want it to (place in code module of your
worksheet):

Option Explicit
Private mlngSavedRowCnt As Long
Private Sub Worksheet_Activate()
'Save row count when worksheet is activated
mlngSavedRowCnt = Me.UsedRange.Rows.Count
End Sub
Private Sub Worksheet_Change(ByVal rngTarget As Range)
'Check if number of rows has changed
If Me.UsedRange.Rows.Count <> mlngSavedRowCnt Then
MsgBox "Number of used rows in the worksheet """ & Me.Name _
& """ changed from " & CStr(mlngSavedRowCnt) & " to " _
& CStr(Me.UsedRange.Rows.Count)

'Do stuff here

'Save the new row count
mlngSavedRowCnt = Me.UsedRange.Rows.Count
End If
End Sub


Even though it's no problem to create custom properties of a worksheet
if you really want to. This should do the exact same as the code above
(place in code module of your worksheet):

Option Explicit
Private mlngSavedRowCnt As Long
Property Get SavedRowCount() As Long
'Return the saved row count
SavedRowCount = mlngSavedRowCnt
End Property
Property Let SavedRowCount(cnt As Long)
'Save row count
mlngSavedRowCnt = cnt
End Property
Property Get CurrentRowCount() As Long
'Return the current row count
CurrentRowCount = Me.UsedRange.Rows.Count
End Property
Private Sub Worksheet_Activate()
'Save row count when worksheet is activated
mlngSavedRowCnt = Me.CurrentRowCount
End Sub
Private Sub Worksheet_Change(ByVal rngTarget As Range)
'Check if number of rows has changed
If Me.CurrentRowCount <> Me.SavedRowCount Then
MsgBox "Number of used rows in the worksheet """ & Me.Name _
& """ changed from " & CStr(Me.SavedRowCount) & " to " _
& CStr(Me.CurrentRowCount)
'Do stuff here
'Save the new row count
Me.SavedRowCount = Me.CurrentRowCount
End If
End Sub


Let me know if this helps, best regards
Peder Schmedling