A worksheet is unexpectedly activated

I have a macro assigned to a button that runs perfectly on the data in the active sheet and in other sheets as well using Excel 2010. However, when I try to run it using Excel 2013 or Excel 2016, a different sheet is unexpectedly activated so the macro generates an error. There is nothing in the code that activates this other sheet and, to be safe, the macro disables events, even though there is nothing in the event code that even references to this other sheet. I have tried assigning the active sheet name to a variable at the beginning of the macro and the using that variable to activate the sheet even though it is already the active sheet, but the other sheet is still activated. I was wondering if anyone else has experienced this problem. I was hoping to get some feedback on what could possibly cause this to happen. As I already mentioned, it runs without errors using Excel 2010. My suspicion is that there is a glitch in the 2013 and 2016 versions of Excel. I haven't posted the code because it is quite long and part of a large project that calculates the final results of a cross country meet. If anyone has any suggestions or would like the code to be posted, I can certainly do that. Many thanks in advance.

Also posted at: https://www.mrexcel.com/board/forums...10/post-thread

And stepping through doesn't allow you to notice the sheet activation change?

I have tried that with no success.

So while stepping through you are not able to see when this other sheet becomes active...

Is there a way to setup a demo file with minimum code to see if it still occurs...Perhaps workbook has become corrupt...

The code works perfectly using Excel 2010 so the workbook is not corrupt.

Of course...you mentioned that...apologies...very strange indeed...

No apologies necessary. It is strange and that's why I think there is a glitch in 2013 and 2016.

Perhaps breaking code down in smaller modules and test in 2013 & 2016...

Thank you for the suggestion. Unfortunately, I can't do that because some lines of code are inter-connected so it would be hard to do. What is very strange is that I open Excel 2010, open the file, click all the buttons and everything works. I close 2010 and repeat those steps using Excel 2013 and I get the errors.

I experienced 2010 vs 2013 glitches with listrows.add code before...So anything is possible...
Is however workarounds but one can only ascertain with file or code snippets unfortunately...

This the code. I should mention that I also have a different macro which generates the same problem.

Please Login or Register  to view this content.

Wow Mumps1...That's a lot of procedure for 1 module...I suggest breaking it down into smaller modules...Also, that code can be simplified a lot...
Are you not able to supply a file and explain requirements...Even just smaller segment requirements which will allow for more simplified code to be supplied...
Somewhere in all of that there is a reason for this issue...

I'm sorry but the macros generate many different errors one of which makes Excel hang and I have to use the Task Manager to close it. I think it would be as frustrating for you as it is for me. Also, it contains a large amount of student data which I cannot make public and would take too long to de-sensitize. If you think that the code can be simplified, could I impose on you to perhaps do that for me and I will give it a try. Maybe that will fix the problem.

Without some kind of sample file, all supplied code will be untested which defeats the purpose...Write a snippet to change student names and confidential data and supply a sample...This is only alternative...Can't see any other way I'm afraid...

OR

Break your code into smaller modules...Referencing specific sheets with WITH statements...And Not With Activesheet...This will ensure code executes for those specific sheets only...

I will see if I can de-sensitize the data. I have to use Activesheet because there are 12 sheets that have a button to run the same macro.

Remove the buttons and make use of a modeless userform or add a button to your ribbon...
Anyway...Making use of With Sheet Specified statements will surely solve your problem...Good Luck...

I have managed to de-desensitize the data. I would respectfully suggest that you save the file before you click any button. The School Name and Student Code are entered manually on site after each race after which the "Calculate Results" button is clicked. This is done for each race. Then the "Finalize Meet Results" button on the "FinalStandings" sheet is clicked. You will notice that there is a "School" sheet with school names and codes. There are 12 sheets, one for each event, a "FinalStandings" sheet and a "RegionalQualifiers" sheet that are populated automatically. The "FinalStandings" sheet and "Labels" sheet have buttons which are self-explanatory. The "Finalize Meet Results" button on the "FinalStandings" sheet and "Create School Copy of File" button on the "Labels" sheet save files to the Desktop. If you have access to Excel 2010, I would suggest that you try it out with that so you can see how it works without errors.

K Cool, a bit late now, but will have a look in the morrow...

Much appreciated.

I notice now why your code always pops up asking for a entry...It activates the "Final Standings" Sheet when buttons are pressed...Does not happen when code is stepped through though...

Also, if you have time...explain your ranking table to the right of data and why some have more entries than others...If the exact requirement can be explained I am so so certain it can be simplified...
Also...Just to understand...Each sheet is selected individually and then button pressed...

I tried stepping through and the problem still exists. The macro looks for blank cells in columns B and C. It asks for an entry even though there are no blank cells in the active sheet because it should be referencing the active sheet, but since the active sheet has changed, it generates the request. I guess you don't have access to Excel 2010?

I think you misread...I also get your issue...Am on 2019 now...will run on 2013 & 2016 tomorrow...

I notice now why your code always pops up asking for a entry...It activates the "Final Standings" Sheet when buttons are pressed...Does not happen when code is stepped through though...

My apologies. Look forward to tomorrow.

Hi Mumps1, please see post 20 regarding RHS Ranking Table...

Hi Mumps1

Have a look at this simplification...This works on all the "*Yr*" Sheets...
I have not incorporated any code that has to do with "Final Standings Sheet" & have removed the protection code...You can add later
Also try and avoid any "Activate" or "On Error" snippets unless really needed...
I suggest adding a snippet as soon as I know what must be done on that sheet with the extracted data...

Also...I advise not having User input Info during Code runs...
Rather do a check and inform if anything needs still to be populated....See red snippet in code below...

Please Login or Register  to view this content.

I suggest also making use of a Modeless Userform to Run the code for each Active Sheet...

@zzzeddy
Thank you for the suggestion. I tried something similar to that and unfortunately, the problem persisted.

@sintek
First of all, thank you so much for helping out with this task. You should know that I am a retired school Principal and started coding on this task as a volunteer more than 10 years ago, before I retired. It took a couple of years of trial and error to get to where it is today. Unfortunately, although Excel is said to be backward compatible, I guess that is not always true, hence the problems. Regarding the ranking table to the right of data: A school must have at least 3 runners in a race to qualify as a team so if a school has only one or two runners, that school will not appear in the Team list to the right. The team points are based on the top 3 runners in each school. Ties are broken based on the position of the first runner in each of the tied teams. Yes, each sheet is selected individually and then the button is pressed. The reason I have the user input data while the code is running is because if a school code is not entered or an invalid school code is entered and the results are calculated, it throughs off both the team results and running totals. The best time to correct this is on site because all the runners and their coaches are present and can be consulted as to the identification of the missing runner. After the meet, this would be very hard to do. If you need any more information, please let me know. Please give me a little time to try and incorporate your suggestions into my coding and I will get back to you.

Thanks for clarification Mumps1...
Code produces the exact same results as yours...so I assume it does work...
With regards to the User Input...If the User is running the system on site, then he is there anyway for missed entries to be filled in again prior to re-running code...Or am I missing something...Anyway...have a look at sample file I attached and we'll take it from there...

PS I added blue snippets in code above to have data left in same order as it started...

With the added blue snippets I get "Application define or Object define error" on this line"

Please Login or Register  to view this content.

Not with my sample file attached above...All sheets run perfectly...

Right you are!!! I should also let you know that RoryA at MrExcel has offered a suggestion that works.
https://www.mrexcel.com/board/thread...vated.1219213/
However, I like your code because it is so much more efficient than mine so I am going to take some time and try to incorporate it with what already works. Thank you so much once again for all your time. It is very much appreciated.

Glad to have helped...The other code that does Whatever for the Total Sheet...can also be simplified...but hey baby steps...If it "ain't broke"...

So true.