Trying to Close File

[Orion Cochrane]

I added some code in an Excel file that told me whether I was done with the
active sheet or not. Basically, if I entered all the information I needed,
the tab colour would be green. Otherwise, it would be red. I used a
Workbook_SheetChange event and called a module that had the code. Upon
closing the file, I got prompted to save. I clicked Yes, it saved, and
reprompted me to save endlessly.

Basically, the code for the colour change was this:

ActiveSheet.Tab.Color = RGB (Insert colour code)

When I go to save, the Saved property is false. I have since taken the
colour code out, and it works fine. What am I missing? I would really like
this to work, as it is very handy as opposed to going to my Summary tab to
see what tabs need info.

TIA.

 

[OssieMac]

Hello Orion,

I think your problem is that the worksheet change event is recalling iself
due to changes being made by the code within the event.

Try putting the following line of code as the first line after the sub name
Application.EnableEvents = False

then put the following line to be processed last in the code

Application.EnableEvents = True

Now there is just one little problem. If during testing,your code fails
after the first line but before reaching the last line then events remain
turned off until an excel restart or you run code to turn it on. Therefore
include the following sub anywhere in the VBA editor and run it from the VBA
editor if this occurs. To run it from the VBA editor, click anywhere in the
sub and press F5. You can leave the sub there for use anytime at a later date
if you are doing modifications to your code.

Sub ReEnableEvents
Application.EnableEvents = True
EndSub