How can I clear a public value when all applications end?

[USAMax]

I have a Worksheet_Change routine that works beautifully except for one Public variable.

The program processes job schedules and when a job schedule goes into next year it duplicates the existing worksheet and when it updates that worksheet the Worksheet_Change kicks in and returns the weeks it processed so this year can continue with the weeks it did not process.

Forgetting all that, I just need this one Public variable to clear when ALL applications have finished. Please tell me this is possible.

 

[Norie]

Can't you just add code to clear it?

 

[USAMax]

I've been thinking about that for a while now. The problem is that Sheet2's Worksheet_Change sets the value so it can return it to Sheet1's Worksheet_Change. I kept thinking I wanted to change it when Sheet1's Worksheet_Change ended but if I put it there it will be at the end of Sheet2's when the worksheet is copied.

I just came up with a way I think will work. I will clear it just before Sheet1 calls Sheet2.

I have never had a Worksheet_Change call a Worksheet_Change before so this is interesting.

If it doesn't work I will let you know.

Thank you Norie,

 

[Norie]

Why are you calling an event?

 

[USAMax]

After the user enters all their information they add the Time-Of-Completion and that triggers the Worksheet_Change.

The project can take up to 16 weeks and if the project's Time-Of-Completion is in February there will still be time left over as it works backwards. The remaining weeks need to be carried over to this year. That is why I need the Public Variable.

There is no room on the worksheet for a button and they don't want to remember any hot keys, that why the Change Event.

My solution worked, I am clearing the variable before it calls the other sheet.

Thank you,

 

[Teeroy]

You should not be able to call an event from outside its sheet code module as they are private by default. Even if you could it would be a bad idea and you would be better to have both events calling the same Sub in a standard code module.