SOLVED

How can I stop the Excel macro engine from selecting a different workbook after running my macro?

Copper Contributor

I just "up"?graded to Microsoft 365 and I find that Microsoft introduced some processing to the (Excel) macro engine that runs after running a macro. It selects whichever workbook was opened first after completing the macro. For example, I open Job Hunting Log 2023.xlsx, which has no macros. Then I open Finance.xlsm, which has macros. I run a macro in Finance.xlsm. After it completes, it activates Job Hunting Log 2023.xlsx, displaying that workbook's current cell. If I open Finance.xlsm first, then open Job Hunting Log 2023.xlsx, then go back to Finance.xlsm to run the macro, Finance.xlsm is the active workbook when the macro completes.

 

I tried adding the following to the end of my macro but it doesn't do any good.

Workbooks("Finance.xlsm").Activate

Apparently the activation of the first opened workbook is done by the macro engine after it completes running the macro.

 

Why would Microsoft pay programmers to add this detrimental code to the macro engine?

 

Is there anything I can do to prevent this behavior and circumvent Microsoft's detrimental programming?

 

BTW, your site has some very poorly designed security.  I got a message that some sort of auto save happened.  When I tried to post, I got an error message.  When I tried to correct and post again, I got another error that I'm trying to flood your site with 2 messages within 300 seconds.  But all I'm trying to do is just post my message once.  How can I be flooding your site when you won't even let me post once?

2 Replies
Perhaps you can post the macro so we can take a look?
best response confirmed by Michael_Jos_Martin (Copper Contributor)
Solution

@Jan Karel Pieterse 

 

Thanks for responding.  This macro has been running successfully for almost 2 decades in various versions of Excel.  I started to copy and paste the macro but it is long and complex with a lot of subroutines.  I decided to dig in some more and found that only my macros that create a combo box and attach some code to it had this issue.  The subroutine that creates the combo box ends with the following.

' Make sure the Excel window is active
  Application.Visible = False
  Application.Visible = True

I put those in there because Excel was making the Visual Basic window the active application when my macro completed.  Long story short, after trying code to make the place I started from active and visible, I researched and found the following to replace the above.

ThisWorkbook.VBProject.VBE.MainWindow.Visible = False

 

Apparently the "Breaking What Ain't Broke" dept. of Microsoft decided to create a project and write code to keep track of which workbook was opened in what order and add processing when the application becomes invisible, then visible that goes to the first spreadsheet opened.

 

BTW this page keeps prompting me with the following messages (the time keeps changing).  There is no save button.

Your content was last saved to auto recover at 09:49 PM. Please save your document, using the save button, regularly to avoid data loss.

1 best response

Accepted Solutions
best response confirmed by Michael_Jos_Martin (Copper Contributor)
Solution

@Jan Karel Pieterse 

 

Thanks for responding.  This macro has been running successfully for almost 2 decades in various versions of Excel.  I started to copy and paste the macro but it is long and complex with a lot of subroutines.  I decided to dig in some more and found that only my macros that create a combo box and attach some code to it had this issue.  The subroutine that creates the combo box ends with the following.

' Make sure the Excel window is active
  Application.Visible = False
  Application.Visible = True

I put those in there because Excel was making the Visual Basic window the active application when my macro completed.  Long story short, after trying code to make the place I started from active and visible, I researched and found the following to replace the above.

ThisWorkbook.VBProject.VBE.MainWindow.Visible = False

 

Apparently the "Breaking What Ain't Broke" dept. of Microsoft decided to create a project and write code to keep track of which workbook was opened in what order and add processing when the application becomes invisible, then visible that goes to the first spreadsheet opened.

 

BTW this page keeps prompting me with the following messages (the time keeps changing).  There is no save button.

Your content was last saved to auto recover at 09:49 PM. Please save your document, using the save button, regularly to avoid data loss.

View solution in original post