Code Runs Even When New Row was not Added to Table - Excel VBA

Question

My code is supposed to be a simple table length check.
If the table length increases, a macro runs.
Unfortunately, it runs when I modify any data in an existing record. But it only happens once!
It occurs when I open the workbook initially and then try to modify anything in the workbook. After which, it runs as it should, only when you added a new entry. Sub is located on the worksheet that contains static cell A1. This is the problem macro. It seems to behave as if there is a difference between the number of rows and the static range. But again, only when you initially open the workbook!

Private Sub Worksheet_Calculate()


    Dim n As Long

    n = GetTableSize()
    If n > LastRowNumber Then NewDatabaseEntry
    ' Always set LastRowNumber so that even after entries are deleted (n < NumRows),
    ' adding new entries will work correctly.
    LastRowNumber = n

End Sub

Supporting macro in a separate module:

Option Explicit

Public LastRowNumber As Long
'This will be used to monitor the number of rows in the
'Projects worksheet. This will then be compared to the
'Value of the formula in the TableSize worksheet.
'The value in TableSize will have a variable 'n'.
Public Function GetTableSize() As Long

  GetTableSize = Worksheets("TableSize").Range("A1").Value2
End Function

This sub is located on the worksheet where the table being monitored resides:

Private Sub Workbook_Open()

  LastRowNumber = GetTableSize()
End Sub

NewDatabaseEntry is the module that alerted me to the fact that there was a problem as it shows a pop-up before it runs. Otherwise, I would've been unaware of there being a problem in the first place. Any help would be appreciated.

Value2 is just faster, isn't it? I use it as default unless I'm pulling dates. — jamheadart
– jamheadart, Commented May 7, 2018 at 17:57
Now that you have your question answered you can also use ThisWorkbook.Sheets("Sheet1").ListObjects("Records").ListRows.Count to get the total Rows on the Table instead of a formula — Ricardo A
– Ricardo A, Commented May 8, 2018 at 19:53
That is very strange, i get the total rows from my table with just that line. I am in excel 2013 i am not sure if this changes for your version. I guess the formula will have to do for now. — Ricardo A
– Ricardo A, Commented May 8, 2018 at 20:33

Mistella · Accepted Answer · 2018-05-07 18:10:45Z

1

If your Workbook_Open sub is in a sheet module, then it's not being called when the workbook opens. Your LastRowNumber variable is defaulting to a value of zero - 0. Then, when you make any change to the data, since n > 0, it calls NewDatabaseEntry. After the first time, the LastRowNumber variable has the value it's supposed to have, and everything works as expected (if I understood your explanation correctly).

Instead, the Workbook_Open sub needs to be in the workbook module - ThisWorkbook. Event-code has to be in it's expected location to be called correctly.

(I almost added this as a comment, then realized this is probably what's causing the issue...)

answered May 7, 2018 at 18:10

Mistella

1,7482 gold badges13 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Remi Over a year ago

Crying tear of joy. I had this issue for over a month and just did not see that it said Workbook_Open on that module. Moved it into ThisWorkbook Excel Object and problem solved! Thank you.

Collectives™ on Stack Overflow

Code Runs Even When New Row was not Added to Table - Excel VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related