Delay Private Sub Worksheet_Change(ByVal Target As Range)

[jorjacman]

Hi all,

Thanks in advance for taking the time to view this topic. I am wondering how to approach the following problem:

I have a worksheet that has a macro written it its sheet code that runs if any of the cells within the worksheet change. I use the following method:

Please Login or Register  to view this content.

Now, I don't want this macro to run right when I open up the file, like it is currently. There are certain fields that need to populate from an outside source and I don't want this change in worksheet to trigger the macro.

Is there a way to have the Private Sub Worksheet_Change delayed from running a certain time after the workbook has been opened? I already have another macro that triggers when the workbook is opened so I wasn't sure how to reference this specific sheet macro.

Thanks again!

[Norie]

How is that code being triggered?

Is it from code that's executed when the workbook is opened?

[jorjacman]

This is how the workbook is setup:

The general workbook has the following macro. When I open the workbook, it waits for a set amount of time (10 seconds in this case) and then calls the "In_Season" macro.

Please Login or Register  to view this content.

The purpose of In_Season is to send an email if any of the cells contain a word like "alert". It sends both the row and column names.

Please Login or Register  to view this content.

So when the workbook opens, it waits ten seconds and calls the above code.

I also want the above code to be executed when a cell value in that worksheet changes. So if a different combination has an alert, an email will trigger with the latest update. Hence the following:

Please Login or Register  to view this content.

So whenever that worksheet changes, an email is automatically sent.

However, I would like for there to also be a delay in the above code so it doesn't run automatically when the workbook is opened.

Let me know if I need to clarify anything. I have attached the spreadsheet for reference. Thanks!Macro_Testing.xlsm

[Norie]

I can't see how the Change event is being triggered when the workbook is opened.

For that event to be triggered a change has to take place on the worksheet, and the code you've posted doesn't make any changes to any worksheet.

Are you sure it's the Change event that's running at start up?

I ask because the code for the event are identical to the sub In_Season that you are setting up to run 10 secs after the workbook is opened.

[jorjacman]

That's the issue. The Change event is NOT being triggered when the workbook is opened. It is only triggered when there is a change to that worksheet. Here is the flow:

1. Open Workbook
2. Workbook waits ten seconds then executes In_Season one time through Private Sub Workbook_Open().
3. Nothing else happens UNLESS a value in the worksheet changes. Therefore, Private Sub Worksheet_Change(ByVal Target As Range) is dormant until a value changes. Once a change is made, In_Season is triggered.

If you replace the email with your email (and have Outlook setup), you will see what I mean. After opening the workbook, an initial email is sent. Then if a change is made to the worksheet, another email will be sent.

[Norie]

Sorry I'm not following.

You want to delay the change event but the change event isn't triggered until you change something on the worksheet, so don't change anything on the sheet and it won't be triggered.

PS I tried the workbook and In_Season was triggered after 10 seconds, without any change being made to the worksheet.

[jorjacman]

It's OK, thanks for your patience. The spreadsheet I have attached is simply a prototype for a much more complex, personal file.

It is good that In_Season was triggered after 10 seconds without any change. That is intended, hence the

Please Login or Register  to view this content.

The purpose of this is to establish a "starting point" and email the starting condition.

Now you say, "You want to delay the change event but the change event isn't triggered until you change something on the worksheet, so don't change anything on the sheet and it won't be triggered."

The problem is that the fields in that worksheet are actually automatically populated by an external source. The file I attached simply represents what it would look like after it's populated. So when you first open the workbook there may be a few #VALUE! or #NAME? values but, after a few seconds, the matrix will be clear of those errors and have proper values. Because the worksheet changes from those weird initial values to correct ones, I want the Change event to delay some time until the matrix is correctly populated. Then it will trigger after if there are any changes.

Make sense? Haha

[Norie]

Normally to prevent an event being triggered we would disable events, but I'm not sure how/if that would affect your OnTime.

Anyway, to disable events add this to the workbook open sub.

Please Login or Register  to view this content.

To re-enable events use this.

Please Login or Register  to view this content.

You could run that manually in the Immediate Window (CTRL+G), or you could set up a small sub that you can call, perhaps from a button, or you might event want to set up another OnTime to re-enable events after a set period has elapsed.

[humdingaling]

can you try an layered approach?

Please Login or Register  to view this content.

workbook opens starts trigger subroutine

Please Login or Register  to view this content.

trigger changes a cell in worksheet

then

Please Login or Register  to view this content.