Cannot remove the effects of deleted Auto_Close()

**daivddd** · 04-17-2024, 02:50 PM

Fitness contains 1 sheet to monitor weight loss. The first 5 columns remain stationery indicating planned period by period target weights. The next 2 columns show the targets adjusted as desired to be realistic and compared to the originals. Actual weight is manually entered periodically in the 8th column with results automatically appearing in the following 4 columns conditionally formatted green, black, or red depending on if positive, unchanged, or negative. The current day target and actual figures conditionally turn orange.

The workbook is VBA'ized only to run automatically upon opening and calls a sub that causes a couple of cells to continuously blink alternating colors as well as to manually close the workbook without saving changes if desired without a close prompt appearing.

It all works perfectly...unless an unrelated Workbook2 is open at the same time in which case Fitness immediately reopens once closed. It can never be closed permanently, whether saved or not, unless Workbook2 has first been closed.

So I conceded to live with a close prompt and deleted the Auto_Close sub. However, my baffling problem is that now even with no Auto_Close, the computer restarted, and a close prompt appearing, Fitness continues to always immediately restart once closed, whether saved or not, if Workbook2 is open. Only after Workbook2 is closed can I close Fitness.

I had no problem before I first added Auto_Close so that seems to have caused the problem. Why does the problem continue even though Auto_Close is gone?

Workbook_Open is in ThisWorkbook of Fitness and calls the StartBlink sub which is in Module1. Module2 contains (which I later removed) the Auto_Close() sub which actuates ThisWorkbook.Saved = True.

Thanks for you help!

(I'm new here. If Fitness.xlsm is not attached, please let me know and I'll try again)

**TMS** · 04-17-2024, 06:01 PM

You have an OnTime (StartBlink) event which you call from the Workbook_Open event handler. I see no evidence of there being any "StopBlink" code.

I suspect that what is happening is that you start the recursive StartBlink code and it continues to call itself ... even after the workbook has been closed. That means the workbook will be re-opened to run the code.

I think you will need to create and run a StopBlink subroutine using the scheduled time (as held in xTime). This is just a guess because it's not something I tend to do. But I think you should be able to create such a module and feed it the scheduled time. Ideally, this subroutine shoud always be called from the Close_Workbook event handler.

**daivddd** · 04-18-2024, 12:19 AM

Being a recursive code and calling itself even after closing the Workbook makes sense. Now I'd like to stop the recursive code and forget all about closing without a prompt. But I can't eliminate it. I remove the event handler, get the workbook saved and closed, then reopen it and the recursive code is still in control.

You might have answered my next request already but I didn't understand. I hope you can tell me how to use a schedculed time and xTime in order to stop that stupid recursive code!

Thank you if you can!!

**TMS** · 04-18-2024, 11:56 AM

I wouldn't thank you for doing this in any workbook I needed to use, but ...

Typically, you would do it like this:

In the Workbook Module:

Please Login or Register  to view this content.

In Standard Module (note that xTime1 is a Public variable before the code):

Please Login or Register  to view this content.

In the Immediate Window, you would see something like this:

PHP Code:


Open start:                     0      
StartBlink start:            0      
StartBlink end:              45400.6940277778      
Open end:                      45400.6940277778      
StartBlink start:            45400.6940277778      
StartBlink end:              45400.6940393519      
StartBlink start:            45400.6940393519      
StartBlink end:              45400.6940509259      
StartBlink start:            45400.6940509259      
StartBlink end:              45400.6940625      
StartBlink start:            45400.6940625      
StartBlink end:              45400.6940740741      
StartBlink start:            45400.6940740741      
StartBlink end:              45400.6940856482      
StartBlink start:            45400.6940856482      
StartBlink end:              45400.6940972222      
StartBlink start:            45400.6940972222      
StartBlink end:              45400.6941087963      
StartBlink start:            45400.6941087963      
StartBlink end:              45400.6941203704      
StartBlink start:            45400.6941203704      
StartBlink end:              45400.6941319444      
StartBlink start:            45400.6941319444      
StartBlink end:              45400.6941435185      
StartBlink start:            45400.6941435185      
StartBlink end:              45400.6941550926      
StartBlink start:            45400.6941550926      
StartBlink end:              45400.6941666667      
StartBlink start:            45400.6941666667      
StartBlink end:              45400.6941782407      
StartBlink start:            45400.6941782407      
StartBlink end:              45400.6941898148      
StartBlink start:            45400.6941898148      
StartBlink end:              45400.6942013889      
StartBlink start:            45400.6942013889      
StartBlink end:              45400.694212963      
StartBlink start:            45400.694212963      
StartBlink end:              45400.694224537      
StartBlink start:            45400.694224537      
StartBlink end:              45400.6942361111      
StartBlink start:            45400.6942361111      
StartBlink end:              45400.6942476852      
StartBlink start:            45400.6942476852      
StartBlink end:              45400.6942592593      
StartBlink start:            45400.6942592593      
StartBlink end:              45400.6942708333      
StartBlink start:            45400.6942708333      
StartBlink end:              45400.6942824074      
StartBlink start:            45400.6942824074      
StartBlink end:              45400.6942939815      
StartBlink start:            45400.6942939815      
StartBlink end:              45400.6943055556      
StartBlink start:            45400.6943055556      
StartBlink end:              45400.6943171296      
StartBlink start:            45400.6943171296      
StartBlink end:              45400.6943287037      
StartBlink start:            45400.6943287037      
StartBlink end:              45400.6943402778      
StartBlink start:            45400.6943402778      
StartBlink end:              45400.6943518519      
StartBlink start:            45400.6943518519      
StartBlink end:              45400.6943634259      
StartBlink start:            45400.6943634259      
StartBlink end:              45400.694375      
StartBlink start:            45400.694375      
StartBlink end:              45400.6943865741      
StartBlink start:            45400.6943865741      
StartBlink end:              45400.6943981482      
StartBlink start:            45400.6943981482      
StartBlink end:              45400.6944097222      
StartBlink start:            45400.6944097222      
StartBlink end:              45400.6944212963      
StartBlink start:            45400.6944212963      
StartBlink end:              45400.6944328704      
StartBlink start:            45400.6944328704      
StartBlink end:              45400.6944444444      
StartBlink start:            45400.6944444444      
StartBlink end:              45400.6944560185      
StartBlink start:            45400.6944560185      
StartBlink end:              45400.6944675926      
StartBlink start:            45400.6944675926      
StartBlink end:              45400.6944791667      
StartBlink start:            45400.6944791667      
StartBlink end:              45400.6944907407      
StartBlink start:            45400.6944907407      
StartBlink end:              45400.6945023148      
StartBlink start:            45400.6945023148      
StartBlink end:              45400.6945138889      
StartBlink start:            45400.6945138889      
StartBlink end:              45400.694525463      
StartBlink start:            45400.694525463      
StartBlink end:              45400.694537037      
StartBlink start:            45400.694537037      
StartBlink end:              45400.6945486111      
StartBlink start:            45400.6945486111      
StartBlink end:              45400.6945601852      
StartBlink start:            45400.6945601852      
StartBlink end:              45400.6945717593      
StartBlink start:            45400.6945717593      
StartBlink end:              45400.6945833333      
StartBlink start:            45400.6945833333      
StartBlink end:              45400.6945949074      
StartBlink start:            45400.6945949074      
StartBlink end:              45400.6946064815      
StartBlink start:            45400.6946064815      
StartBlink end:              45400.6946180556      
StartBlink start:            45400.6946180556      
StartBlink end:              45400.6946296296      
StartBlink start:            45400.6946296296      
StartBlink end:              45400.6946412037      
StartBlink start:            45400.6946412037      
StartBlink end:              45400.6946527778      
BeforeClose start:           45400.6946527778      
StopBlink start:             45400.6946527778      
StopBlink end:               45400.6946527778      
BeforeClose end:             45400.6946527778

**daivddd** · 04-18-2024, 09:23 PM

OMG, Guru, I have no idea what I just did but it worked. I can now run Fitness and close it as intended without saving even if another unrelated workbook is open. Previously I could not reverse the recursive loop I created and was hopelessly stuck! I'm relieved now and in a good mood again.

I thank you greatly for taking time to resolve this problem for me!

**TMS** · 04-19-2024, 03:15 AM

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**daivddd** · 04-20-2024, 12:10 AM

TMS, the Startblink sub no long automatically starts when I open Fitness because I pasted over and removed WorkBook_Open. I wanted to ask you first so I don't mess up our repair; will there be a conflict if I put it back alongside the xTime code you gave me? This is the one-line sub I used to have in ThisWorkbook:

Please Login or Register  to view this content.

**TMS** · 04-20-2024, 02:43 AM

Shouldn't be a problem. You don't actually need "Call" but it doesn't hurt. I only added the Debug statements so you could see how xTime1 was being updated.

**daivddd** · 04-20-2024, 05:47 AM

That did not work. I could again open Fitness with blinking, but I could no longer close the workbook. I give up.

Since I cannot both open with blinking and close without the prompt, I removed Auto_Close. But since then I'm forced recursve if I open blinking, I removed blinking also. In other words I delected all macros and have no blinking and I close with a prompt. Bummer

I liked my blinking colors.

Thanks anyhow.

**AliGW** · 04-20-2024, 05:50 AM

You are probably better without them if your workbook is for wider use - read up about photo-sensitive epilepsy:

https://www.epilepsy.com/what-is-epi...otosensitivity

**TMS** · 04-20-2024, 05:57 AM

@Ali: I agree with you. In this case, it's unlikely to be an issue as it's only three cells changing font colour.

I wouldn't do it and I don't see the benefit. Just because you can do something doesn't mean you should.

Not near a PC at the minute but I’ll try and post a working example later.

**AliGW** · 04-20-2024, 06:06 AM

In this case, it's unlikely to be an issue as it's only three cells changing font colour.

I respectfully disagree: if the photosensitivity is severe enough, then ANY blinking can trigger it. This is why TV programmes now have to carry a warning.

**TMS** · 04-20-2024, 06:17 PM

Here is the working example. Your worksheets and data. My code. I have renamed Sheet3 as "Control" and added a Named Range, "GetOutOfJail", refering to cell A1 on the Control Sheet. If you type "exit" (no quotes) in the GetOutOfJail cell, it will prevent the StartBlink subroutine from being scheduled.

I have commented out all the "progress" Debug statements. I have added a couple of MsgBoxes to confirm that blinking is stopped prior to exiting. They can also be commented out once you are content that this workbook operates as expected.

**TMS** · 04-20-2024, 06:18 PM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**TMS** · 04-20-2024, 06:25 PM

Forgor to mention: because your Before_Close routine marks the Workbook as Saved, any changes you make to the data, structure, code, or whatever, MUST be saved manually before closing the workbook or they will be lost.

This may be the problem you experienced. The workbook will never ask you if you want/need to save it, no matter what changes you make.

Changing the formatting of the cells will immediately mark the workbook as "dirty" (every second), but your code over-rides that.

Cannot remove the effects of deleted Auto_Close()

LinkBack

Thread Tools

Rate This Thread

Display

Cannot remove the effects of deleted Auto_Close()

Re: Cannot remove the effects of deleted Auto_Close()

Re: Cannot remove the effects of deleted Auto_Close()

Re: Cannot remove the effects of deleted Auto_Close()

Re: Cannot remove the effects of deleted Auto_Close()

Re: Cannot remove the effects of deleted Auto_Close()

Re: Cannot remove the effects of deleted Auto_Close()

Re: Cannot remove the effects of deleted Auto_Close()

Re: Cannot remove the effects of deleted Auto_Close()

Re: Cannot remove the effects of deleted Auto_Close()

Re: Cannot remove the effects of deleted Auto_Close()

Re: Cannot remove the effects of deleted Auto_Close()

Re: Cannot remove the effects of deleted Auto_Close()

Re: Cannot remove the effects of deleted Auto_Close()

Re: Cannot remove the effects of deleted Auto_Close()

Thread Information

Users Browsing this Thread

Similar Threads

Macro VBA - Remove empty rows and columns with total deleted in msg

[SOLVED] Remove Conditional Formats but not the Effects

[SOLVED] How to remove a deleted named range completely

Button to Remove duplicate and pasting the deleted row in another sheet

Auto_Close

Remove Entire subtotal row will be deleted

Auto_Close() not running

Bookmarks

Bookmarks

Posting Permissions