Forum Discussion
How can I stop the Excel macro engine from selecting a different workbook after running my macro?
- Mar 16, 2024
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 = TrueI 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.
- Michael_Jos_MartinMar 16, 2024Copper Contributor
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 = TrueI 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.