Public variable not storing
 

I set the number of rows on opening a file but when I detect a change in a
sheet, the number of rows is empty. Help please!

The code for ThisWorkbook:
Public NrowsSt As Integer
Private Sub Workbook_Open()
NrowsSt = Range("MyRange").Rows.Count
MsgBox NrowsSt
End Sub


The code for Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox NrowsSt
Nrows = Range("MyRange").Rows.Count
If Nrows < NrowsSt Then
If Nrows NrowsSt Then
MsgBox "No of rows inserted =" & Nrows - NrowsSt
Else
MsgBox "No of rows deleted =" & NrowsSt - Nrows
End If
End If
End Sub

Public variable not storing
 

You have declared NRowsSt in ThisWorkbook code module, therefore when
referencing it you need to qualify with the calls module name, i,e,

If NRows ThisWorkbook.NRowsSt

By not doing so,VBA is implicitly declaring a new public variable called
NRowSt, which is obviously empty as you do not load it.

You should have Option Explicit at the head of your code, this would
highlight such problems.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Gleam" wrote in message
...
I set the number of rows on opening a file but when I detect a change in a
sheet, the number of rows is empty. Help please!

The code for ThisWorkbook:
Public NrowsSt As Integer
Private Sub Workbook_Open()
NrowsSt = Range("MyRange").Rows.Count
MsgBox NrowsSt
End Sub


The code for Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox NrowsSt
Nrows = Range("MyRange").Rows.Count
If Nrows < NrowsSt Then
If Nrows NrowsSt Then
MsgBox "No of rows inserted =" & Nrows - NrowsSt
Else
MsgBox "No of rows deleted =" & NrowsSt - Nrows
End If
End If
End Sub

Public variable not storing
 

Found the answer via a Chip Pearson posting on Google Groups:

ThisWorkbook.NrowsSt is needed on the worksheet.

Thank you Chip Pearson.

"Gleam" wrote:

I set the number of rows on opening a file but when I detect a change in a
sheet, the number of rows is empty. Help please!

The code for ThisWorkbook:
Public NrowsSt As Integer
Private Sub Workbook_Open()
NrowsSt = Range("MyRange").Rows.Count
MsgBox NrowsSt
End Sub


The code for Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox NrowsSt
Nrows = Range("MyRange").Rows.Count
If Nrows < NrowsSt Then
If Nrows NrowsSt Then
MsgBox "No of rows inserted =" & Nrows - NrowsSt
Else
MsgBox "No of rows deleted =" & NrowsSt - Nrows
End If
End If
End Sub

Public variable not storing
 

Read my post. There is more info there.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Gleam" wrote in message
...
Found the answer via a Chip Pearson posting on Google Groups:

ThisWorkbook.NrowsSt is needed on the worksheet.

Thank you Chip Pearson.

"Gleam" wrote:

I set the number of rows on opening a file but when I detect a change in
a
sheet, the number of rows is empty. Help please!

The code for ThisWorkbook:
Public NrowsSt As Integer
Private Sub Workbook_Open()
NrowsSt = Range("MyRange").Rows.Count
MsgBox NrowsSt
End Sub


The code for Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox NrowsSt
Nrows = Range("MyRange").Rows.Count
If Nrows < NrowsSt Then
If Nrows NrowsSt Then
MsgBox "No of rows inserted =" & Nrows - NrowsSt
Else
MsgBox "No of rows deleted =" & NrowsSt - Nrows
End If
End If
End Sub