I have developed an App for a client that is used by multiple Account Managers to generate reports for their clients. Currently the code is setup to save individual copies of the file for each client. Since an Account Manager may have as many as 20 clients, I have developed a code module to support future upgrades of the App that systematically imports data from existing files. During this process, there are 3 different Excel files that are opened. For simplicity, File A is the new program file, after code is initiated, that file is renamed to a Temporary version and all code is executed from that file. File B is the existing Account file and File C is a copy of File A where data is written to. When the process completes for each file, a copy of the original is saved as a Backup and File C is saved to the file name of File B.
Throughout this process, Files B and C are opened and then subsequently Activated numerous times as data is selected and then copied to the new version of the program. I have used Application.ScreenUpdating = False and maybe I just haven't found all instances where that would get overridden as the program moves from one workbook to another but it is not getting the job done.
In an effort to resolve this screen flashing and also to provide the user with information about the progression of the update program, I built a Modeless Userform that lets the user know how many files they have chosen to update and the current status of the update program. My problem is keeping that UserForm on top as the code moves from WorkBook to WorkBook.
I have researched via Google extensively for a solution and read many supposed solutions. I tried Application.WindowState = xlMinimized but I still get screen flashing. I found a supposed solution here on MrExcel at the following link: http://www.mrexcel.com/forum/excel-questions/386643-userform-always-top.html. I added that code to my app and for a single instance it seemed to work as desired. I made a couple of modifications to my UserForm (to ensure correct display of pertinent variables) and then all seemed to go wrong. When I ran my code, Excel crashed. Upon reopening Excel I received a message that there was an error in my Modeless UserForm and it couldn't recover.
What I didn't notice when choosing to use that recommended solution is that those posts were from 2009 which was prior to SDI implementation so I really shouldn't have tried using that solution.
I can try Loading the UserForm and then after Opening/Activating individual WorkBooks have a line of code to Show the UserForm. But this still causes screen flashing from WorkBook to the UserForm.
I have seen a solution that opens a separate instance of Excel and then hiding that instance but I'm concerned about something failing and then leaving the hidden instance running. Is there a way to open Files B and C so that they are hidden as they are opened?
Any and all suggestions are appreciated!
Throughout this process, Files B and C are opened and then subsequently Activated numerous times as data is selected and then copied to the new version of the program. I have used Application.ScreenUpdating = False and maybe I just haven't found all instances where that would get overridden as the program moves from one workbook to another but it is not getting the job done.
In an effort to resolve this screen flashing and also to provide the user with information about the progression of the update program, I built a Modeless Userform that lets the user know how many files they have chosen to update and the current status of the update program. My problem is keeping that UserForm on top as the code moves from WorkBook to WorkBook.
I have researched via Google extensively for a solution and read many supposed solutions. I tried Application.WindowState = xlMinimized but I still get screen flashing. I found a supposed solution here on MrExcel at the following link: http://www.mrexcel.com/forum/excel-questions/386643-userform-always-top.html. I added that code to my app and for a single instance it seemed to work as desired. I made a couple of modifications to my UserForm (to ensure correct display of pertinent variables) and then all seemed to go wrong. When I ran my code, Excel crashed. Upon reopening Excel I received a message that there was an error in my Modeless UserForm and it couldn't recover.
What I didn't notice when choosing to use that recommended solution is that those posts were from 2009 which was prior to SDI implementation so I really shouldn't have tried using that solution.
I can try Loading the UserForm and then after Opening/Activating individual WorkBooks have a line of code to Show the UserForm. But this still causes screen flashing from WorkBook to the UserForm.
I have seen a solution that opens a separate instance of Excel and then hiding that instance but I'm concerned about something failing and then leaving the hidden instance running. Is there a way to open Files B and C so that they are hidden as they are opened?
Any and all suggestions are appreciated!
