ExcelBanter - Help with PROPERTY GET

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Help with PROPERTY GET (https://www.excelbanter.com/excel-programming/422692-help-property-get.html)

Help with PROPERTY GET

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

Help with PROPERTY GET

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
==============================

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.

Help with PROPERTY GET

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:

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

Private Sub Worksheet_Activate()

' How do I get OrigRows in the function above to the code next?

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

End Sub

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.

Help with PROPERTY GET

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

On Jan 20, 8:39*am, wrote:
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:

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

Private Sub Worksheet_Activate()

* * * ' How do I get OrigRows in the function above to the code next?

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

End Sub

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.