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

 

[Leith Ross]

2003/2007

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

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

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

I have never used a Propery Get. How can I weave the code belo
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 easil
accomplish what you want by using a Function...

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

--
Leith Ros

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/

 

[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

 

[Leith Ross]

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 i
activated and
(2) be available to Private Sub Worksheet_Change.

How can I do this to make the following work? (I need OrigRows to hav
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

Hello EagleOne,

The function I presented needs to reside in a standard VBA module. Thi
will make it available to all other modules and procedures in your VB
project. You placed the function definition within the body of
Worksheet event procedure. In VBA you rarely need to select or activat
an object to perform to read or write data. Perhaps it would be best i
you post your workbook on my site. I can then add the code so you ca
see how this is done

--
Leith Ros

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/

 

[smartin]

Leith,

THe code that I think is close to working has been presented.


OK, I must not be asking the correct/logical question.

My w/s is protected; but the User can insert Rows.

I would like to have Excel automatically remove the protection
for the cells in the new row so that the cells (in all columns) can be populated with data.

That said, I do not wish the User to ba able to change the data (except in certain columns which are
not protected) in the cells that were their prior to the inserted row data.

Truly, I am lost as to the best way to do this. Is there a way to do this?

I have played with worksheet events but I am having a problem obtaining the original W/S row count
and comparing that to the row count after the row insertion. My idea was:
IF Sheets("Trial Balance Current").Rows.Count > OrigRows Then
"Reformat only the added cells via the row insertion"

I am almost there but no home run yet.

Any help greatly appreciated!! Eagleone


The unsuccessful code that I have is:

Maybe,

Place this in a standard module:

Function OrigRows() As Long
Static DuplicateCall as Boolean
Static StoredResult as Long
If DuplicateCall = false then
StoredResult = _
Sheets("Trial Balance Current").UsedRange.Rows.Count
DuplicateCall = True
EndIf
OrigRows = StoredResult
End Function

Call as needed from worksheet module.

 

[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