PDA

View Full Version : [SOLVED] On Open: "This action will reset your project, proceed anyway?"



MrRhodes2004
12-29-2016, 03:26 PM
Message: "This action will reset your project, proceed anyway?"
What kind of code-invoked actions might cause this?
This message occurs often. First, it might appear (but not always) when I first load the workbook. I also get it while working in the VBA code as well. This part confuses me even more since it happens when I just editing the code and NOT actively running code. :banghead:

I have been googling for a bit and not found positive answers. At first, I thought it was possible Const that I had created or that I was using AppActivate to retain focus. I have now either deleted or commented those out.

I put error handling into almost every sub and function but those are not catching whatever is trying to reset the project.

However, when I say continue, there doesn't seem to be any problems. However, this causes a problem for the user as they do not know what to do.
Thoughts?

Logit
12-29-2016, 04:05 PM
Most of the posts I read related to this error message seem to focus on using Break Points during programming tests. After removing the Break Points, some remain in "ghost mode" (not a technical term). Others went through their code
and re-erased / re-removed the Break Points ... some Re-compiled their project ... some copied and pasted their code into a new project.

The use of CTRL / BREAK as a method to interrupt the code process also seems to be a focal point.

A few folks were in agreement to use this statement at the beginning of your code :
Application.EnableCancelKey = xlDisabled
More research might be in order regarding this command.

So,overall no one seems to have a definitive explanation but here's a few links:

https://msdn.microsoft.com/en-us/library/office/gg264460.aspx


After selecting Remove all Breakpoints did you also recompile and save the VBA project? I have seen a related problem within Word VBA where previously removed breakpoints are still causing a break. – Dirk Vollmar (http://stackoverflow.com/users/40347/dirk-vollmar)Jan 28 '10 at 13:19 (http://stackoverflow.com/questions/2154699/excel-vba-app-stops-spontaneously-with-message-code-execution-has-been-halted#comment2097634_2154699)


Marcus from London Says:
Friday, 24th June, 2011 at 9:20 am (https://smurfonspreadsheets.wordpress.com/2011/06/23/code-execution-has-been-interrupted/#comment-17017) | Reply (https://smurfonspreadsheets.wordpress.com/2011/06/23/code-execution-has-been-interrupted/?replytocom=17017#respond)Yup, I’ve had this several times in the past as well.What I noticed is that the ‘interruption’ occurred on lines where I had previously had break points.
Resetting and then removing the break points didn’t work.What did work for me (YMMV) is restarting Excel (flush the memory) then exporting and re-importing all the objects (modules, class & forms) which seemed to clear up this ‘phantom’ break point.Cheers – Marcus


TheVBCoder (http://n/a) Says:
Thursday, 12th July, 2012 at 12:57 pm (https://smurfonspreadsheets.wordpress.com/2011/06/23/code-execution-has-been-interrupted/#comment-18792) | Reply (https://smurfonspreadsheets.wordpress.com/2011/06/23/code-execution-has-been-interrupted/?replytocom=18792#respond)Esc or Ctrl-Break is a normal feature of Excel. It allows the programmer to break execution of the code in the event during development it gets stuck in a loop. Normal users certainly should not be hitting Ctrl-Break. Of course they are expected to hit Esc so in that case this issue can pose a problem. The problem is easily repeatable by simply hiting Esc when you know your code is still executing code. Test it by forcing a 1 or 2 sec code to run and hit Esc several times. 2 times out of 10 you will get this message. So the solution to me is 1) Make your code run faster. or 2) use the Application.EnableCancelKey = xlDisabled before the slow code and the xlInterrupt when you are done.

Paul_Hossler
12-29-2016, 05:11 PM
Message: "This action will reset your project, proceed anyway?"
What kind of code-invoked actions might cause this?
This message occurs often. First, it might appear (but not always) when I first load the workbook. I also get it while working in the VBA code as well. This part confuses me even more since it happens when I just editing the code and NOT actively running code. :banghead:

I have been googling for a bit and not found positive answers. At first, I thought it was possible Const that I had created or that I was using AppActivate to retain focus. I have now either deleted or commented those out.

I put error handling into almost every sub and function but those are not catching whatever is trying to reset the project.

However, when I say continue, there doesn't seem to be any problems. However, this causes a problem for the user as they do not know what to do.
Thoughts?



Try Rob Bovey's code cleaner - it fixes all sorts of weird problems for me




During the process of creating VBA programs a lot of junk code builds up in your files. If you don't clean your files periodically you will begin to experience strange problems caused by this extra baggage. Cleaning a project involves exporting the contents of all its VBComponents to text files, deleting the components and then importing the components back from the text files.



http://www.appspro.com/Utilities/CodeCleaner.htm

MrRhodes2004
12-30-2016, 03:03 AM
Thanks Logit
I have been exporting all my modules and forms on a regular basis. I have also been trying to delete breakpoints. But maybe I try them in that specific order. I have been using "Clear All Break Points" but maybe I will do this prior to export and re-import. I was also not saving the file without mods and forms and closing excel. Maybe I will try this as well. Now if I knew how to comment out all code in my sheets before doing this it would be even better.

I have seen the Application.EnableCancelKey = xlDisabled before in a couple of other forums. If I had saved the link, I'd share it. This person was positive it was not useful. However, I will try it at a couple of suspected locations. When resetting the behavior, there are three options. You mentioned xlInterrupt but there is also xlErrorHandler. What are the benefits of either. I would assume that xlInterrupt would break the code but the xlErrorHandler would allow any coded error handling to occur first?

Thanks for your messages. I will go and start testing!

MrRhodes2004
12-30-2016, 03:08 AM
Try Rob Bovey's code cleaner - it fixes all sorts of weird problems for me

http://www.appspro.com/Utilities/CodeCleaner.htm

I have found this website a couple of times before and have always shied away because it clearly states it is not for 64-bit. Unfortunately, it is for 32-bit only. I tried for a bit this morning to force it onto my excel but excel would not have it!
Thanks Paul for the comment. I hope others out there find the tool useful and maybe one day he will advance it to a 64-bit marvel.

MrRhodes2004
12-30-2016, 06:05 AM
In Access there is Application.SetOption "Error Handler", 2. There does not seem to be anything similar in Excel? How would I be able to set the Error Handler level - if it is the user, I would want the option for Unhandled Errors but All errors when I am working in it.

Logit
12-30-2016, 07:48 AM
.
.
Application.EnableCancelKey = xlDisabled:

https://msdn.microsoft.com/en-us/library/office/ff834623.aspx (https://msdn.microsoft.com/en-us/library/office/ff834623.aspx)


==============================================


http://bettersolutions.com/excel/macros/enumerations-xlenablecancelkey.htm


xlEnableCancelKey


Specifies how Microsoft Excel handles Ctrl + Break (or Esc or Command + Period) user interruptions to the running procedure.





xlDisabled
All the keystrokes are ignored and trapping is disabled (0)


xlErrorHandler
The keystroke is sent to the running procedure as an error that is trappable by an error handler using the On Error GoTo statement
This error code is 18 (2)


xlInterrupt*
Allows you to interrupt the macro by pressing (Ctrl + Break) (1)

MrRhodes2004
12-30-2016, 09:27 AM
Thanks for the information. However, I am still getting the message. It is still happening when I am editing code and not (knowingly) running code. It just happened again as I was modifying code, I pressed enter on one line then went to delete the next and the message popped up...
Strange. And frustrating.

MrRhodes2004
12-31-2016, 06:17 AM
I have now placed Debug.Print commands in This Workbook_Open and the first macros that "should" run as the document opens. However, the message still pops up BEFORE ANY of the debug.print commands are executed.
I exported all modules and forms then copied all worksheets to a new workbook and reimported all modules and forms. I was in the process of moving all the stuff over when I got the first message. Really? Ug. Any Ideas?
What is the FIRST line of code that is executed?

SamT
12-31-2016, 06:55 AM
Do you Compile the entire project before Saving the Workbook?

Ensure that in the VBA Tools Menu >> Options >> Editor Tab, that you check all boxes in the Code Settings Frame, and, on the General Tab, you check Break On All Errors.

MrRhodes2004
12-31-2016, 09:30 AM
Do you Compile the entire project before Saving the Workbook?

Ensure that in the VBA Tools Menu >> Options >> Editor Tab, that you check all boxes in the Code Settings Frame, and, on the General Tab, you check Break On All Errors.
I do compile before saving. I also clear all breakpoints as well. I have even tried to export all forms and modules and reimport them prior to save as well.
Now, every time I complete a "Save As and Exit" function through a command button, when the new file is opened it will always trigger the warning.

SamT
12-31-2016, 12:56 PM
Are you using a Personal.xlsm File?

Does it happen when opening the book on a different Machine?

Sometimes, only reinstalling Excel will fix a problem.

MrRhodes2004
01-01-2017, 06:25 AM
Does it happen when opening the book on a different Machine?


The problem is that the file is to be used by many people on many machines. I do not know what settings they have or what versions of Excel they are using. This is one of the main complaints that I have been getting during testing sessions.


However, I have found the following post:
https://forum.solidworks.com/thread/96485


It recommends turning OFF "Notify before state loss". I have tried it on my machine and the results are promising.


How do I control the VBA Options programmatically? How can I turn off "Notify before state loss"? How to I choose which Error Trapping method to use?

SamT
01-01-2017, 08:45 AM
This is one of the main complaints that I have been getting during testing sessions.
Then the problem is in the code. But not necessarily where it stops.

Paul_Hossler
01-01-2017, 08:59 AM
1. Post a WB here if you can

2. Export all modules manually if you have to and see which is the largest one. Try breaking it up into 2 pieces

MrRhodes2004
01-02-2017, 05:20 AM
1. Post a WB here if you can

2. Export all modules manually if you have to and see which is the largest one. Try breaking it up into 2 pieces
Paul,


I have split a couple of the modules into smaller ones.
As for posting the file, it is a large almost 10mb file. It is for my Master Thesis that will be submitted shortly. But I did send you a personal message with a link to the "Beast". The coding is horrible and very ugly. Unfortunately, I do not have time to make it look good. I am trying to make it run smoothly enough for the defense.
Thank you for your input.
Michael

SamT
01-02-2017, 09:36 AM
The coding is horrible and very ugly . . . I am trying to make it run smoothly enough for the defense.
Those two sentiments do not go well together.


DOH! :beg: Has anyone mentioned "Option Explicit?"


Wait. Didn't you say that you got it to error every time it opens? Yep, you did. That's good.

Rename Sub Workbook_Open by one character. Use that Command Button to save and close. Open the Book, then starting with the (Renamed) Workbook_Open Sub, manually step thru the code.

Logit
01-02-2017, 09:53 AM
I've had the same error message a few times in the past.

The use of OPTION EXPLICIT was present in the coding.

Don't know why the error message appeared .... don't understand why it went away on it's own.

I'm very interested in learning if there is a "fix" to this issue. :cuckoo:

MrRhodes2004
01-02-2017, 10:14 AM
DOH! :beg: Has anyone mentioned "Option Explicit?"


I'm very interested in learning if there is a "fix" to this issue. :cuckoo:

I have "Require Variable Declaration" checked in my VBE Options. But yes, I try to make sure that all code starts with an Option Explicit. There are very few good coding practices that I have. That is one of them.
When I UnChecked "Notify before state loss" the majority of the errors went away and have not returned. Therefore, I do not know what specific item was causing the error. It was more of a message to the user. My users do not need to see that. At this moment it worked.

However, since this file goes to multiple people on machine of which I do not have access, how do I control the VBE Options like "Notify before state loss" and the three "Error Handler" options?

SamT
01-02-2017, 12:24 PM
However, since this file goes to multiple people on machine of which I do not have access, how do I control
Good clean code. :dunno

Do you want me to take a look at it?

SamT
01-03-2017, 09:38 PM
I finished reviewing all the code in the Standard Modules, an scanning the rest of the code. Nothing jumped out that might explain the issue, except maybe, possibly the way you use frmBlank. :dunno

I would Load FrmBlank on Opening the book and Unload it on Closing, then, only in each Procedure that took a long time, I would Show it at the beginning, then Hide it at the end. I would not Show it before calling any (A great many) procedure(s). Note that you do not ever explicitly Hide it ATT.

The code in Sub BlankUpdate() is Duplicated in frmBlank Sub UserForm_Activate() but really should only be in Sub UserForm_Initialize(). For that matteer, I would hard code it during Design Time

I will try to go thru the WorkSheet Module's Code soon.

snb
01-04-2017, 01:43 AM
I can't follow this thread.
What information am I missing ?
Why using a public forum for private communication ? To whose benefit ?

The message in the thread title I only get when editing code that is running.
I think the OP isn't aware of the running of the code (started in an event or automatic macro).
Checking the code should start in the codemodule of 'ThisWorkbook'.

MrRhodes2004
01-04-2017, 03:20 AM
Thank you Sam

MrRhodes2004
01-04-2017, 03:53 AM
I can't follow this thread.
What information am I missing ?
Why using a public forum for private communication ? To whose benefit ?

The message in the thread title I only get when editing code that is running.
I think the OP isn't aware of the running of the code (started in an event or automatic macro).
Checking the code should start in the codemodule of 'ThisWorkbook'.

OP here, snb, sorry you are unable to follow the thread. The only 'private' response here, that I know of is the last response from Sam. I had sent Sam a link to my project and he was looking through the code. As the project is not available for public viewing, it is my master thesis, I did not want to post the link publicly. He was graciously responding.
The initial problem was that I was getting a repeated error when opening the file that would state that the project would be reset. I was unable to determine where this error was being thrown because it was originating prior to the first line of code from the ThisWorkbook was called. Through discussion here and additional google search, I learned of the VBE option, "Edit and Continue - Notify before state loss". I had checked this and now I believe that it was possibly the root of the problem as I no longer receive the error.

snb, I may not be a professional coder. Heck, I am not even a sub-par coder. However, I am doing my best to learn from the excellent responses posted here in VBA Express. Yes, I do know that the first code to be run is typically in the ThisWorkbook module. My code uses this extensively and this is why I was having problem debugging it. I had placed a debug.print and msgbox as the first line of code in the open event of ThisWorkbook. Neither were called yet the the error, "This action will reset your project, proceed anyway" was thrown.
Several helpful suggestions were made throughout the discussion here that I have tried.
Again, I apologize that this thread was not clear to you, up to your standards, and that exactly one post was in response to a private message. I hope that the above summary is helpful and meets your approval.

snb
01-04-2017, 07:35 AM
Thank you for explaining what I already knew.
The purpose of a forum is that every visitor can benefit from the 'solutions' posted here.
Nobody is able to benefit from this thread because you do not show any code.
If you want to approach SamT on a private level you can do so; but this forumthread is non-productive in terms of 'forumability'.

If you can't share information to solve your problem you shouldn't use public fora but hire expertise.

SamT
01-04-2017, 08:33 AM
Mike,
I see the Thread is marked Solved. Is that what you want?



snb,
Chill, man, it's a new year. :beerchug:

Logit
01-04-2017, 09:26 AM
MrRhodes2004 (http://www.vbaexpress.com/forum/member.php?2762-MrRhodes2004)

I learned alot from this thread. Thank you for bringing the information to our attention. It will be very helpful in the future.

Happy New Year all !