Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Storing a value to variable | Excel Discussion (Misc queries) | |||
Storing variable in Option.Tag | Excel Programming | |||
Storing a range in a variable | Excel Programming | |||
Storing value in a variable from a cell | Excel Programming |