Help with PROPERTY GET
Leith Ross wrote:
;189670 Wrote:
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
Leith Ross wrote:
;189596 Wrote:
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
Hello EagleOne,
Property Get is a statement used in "Class Modules". You can easily
accomplish what you want by using a Function...
==============================
Function OrigRows() As Long
OrigRows = Sheets("Trial Balance Current").UsedRange.Rows.Count
End Function
==============================
Hello EagleOne,
The function I presented needs to reside in a standard VBA module. This
will make it available to all other modules and procedures in your VBA
project. You placed the function definition within the body of a
Worksheet event procedure. In VBA you rarely need to select or activate
an object to perform to read or write data. Perhaps it would be best if
you post your workbook on my site. I can then add the code so you can
see how this is done.
|