Event macro runs in wrong workbook/doesn't work properly

Hi,
I have a calculate event macro that I need a little help rectifying. The event macro is stored in a sheet and should call 1 of 2 macros depending on if cell A231 is blank or not. The macro seemed to be doing what I need, but it seems there are a few bugs that are causing it to error out/not function the way I need it to.

1. I need the code to run if cell A231 on sheet "Code Sheet" changes. If A231 is blank, I need to run macro1, if A231 is not blank, I need to run macro2. Macro1 and macro2 are stored in a separate module. my event macro is stored in sheet "Code Sheet". Currently, my code is running if ANY cell in my workbook changes. Not good. I only need it to run if A231 on sheet "Code Sheet" calculates

2. The event procedure runs when I open another workbook. So if I have my primary workbook that contains the event macro open, and I open up another workbook, the code tries to run in the other workbook. I do not want this to happen. It also errors out when this happens because the sheet I'm referencing in macro1 and macro2 is not available in the newly opened workbook. How do I stop this? I would think I need to use ThisWorkbook somewhere in my code, but I'm sure sure where/how.

I would GREATLY appreciate input on these 2 issues. I feel I am almost there, but there are just a few little quirks I need help figuring out. Thanks in advance! My code is below:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks for the reply, but this doesn't solve either of my issues. Setting the event as Change rather than Calculate is preventing the event procedure from running . Also, with the above code, the event procedure is still running if I make a change in another workbook. It's just flat out ignoring my A231 reference in my code and saying "I'm going to run this macro if any cell changes anywhere." I've been able to prevent the error message I was getting upon opening a new workbook by inserting ThisWorkbook.Activate at the beginning of my code, but that's still not preventing the macro from running when a change is made to any cell in any workbook. As I said earlier, I just need the procedure to run if A231 on Code Sheet is or is not blank, but right now it runs on any change to any cell anywhere, regardless if the cell that changed is in a separate workbook or not

Originally Posted by JosephP

if a231 contains a formula then as I said a change event won't work. the calculate event will be triggered whenever your sheet calculates for whatever reason. if you have volatile formulas for example then your sheet will recalculate when you open a new workbook or calculate another open workbook.
you do not need to activate the workbook if you make the changes I provided which should have solved your second problem

Thanks JoespehP. Earlier you had mentioned that a change event might be the simplest option, which confused me a bit. I've ran your code with a calculate event and everything seems to be working like I need it. I was misunderstanding how events work and you have clarified that for me. Thank you again for your assistance!