How to Keep a Modeless Excel VBA UserForm Open While Closing Workbooks
Question details
The user needs to prevent a modeless VBA UserForm from automatically closing when a different workbook is closed.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Running a modeless UserForm that contains a timer while simultaneously working with and closing other Excel workbooks.
- Observed behavior
- The UserForm unexpectedly closes when a separate workbook is closed, specifically when the form uses a continuous Do While loop with DoEvents to update a timer label.
Before modifying your VBA code, ensure you have saved a backup of your current macro-enabled workbook (.xlsm) to prevent any loss of your existing UserForm design and scripts.
Use Application.OnTime Instead of DoEvents
Replace the continuous Do While loop with Application.OnTime to prevent interference with the workbook's internal event processing.
A continuous loop using DoEvents can interfere with how the application handles workbook and UserForm events. Using Application.OnTime schedules a macro to run at a specific time, freeing up the main thread and preventing the form from crashing or closing unexpectedly when other workbooks are closed.
Open the VBA Editor (ALT + F11) and remove the 'Do While' loop and 'DoEvents' commands from your UserForm's timer code.
Open a Standard Module (Insert > Module) and declare a public variable at the top to store the timer schedule, for example: 'Public RunTime As Double'.
In the standard module, create a procedure that updates the UserForm and schedules itself using 'Application.OnTime Now + TimeValue("00:00:01"), "UpdateTimer"'.
Inside your new timer procedure, add a check to ensure the UserForm is still loaded before attempting to update its labels to prevent runtime errors.
In the UserForm module, use the 'UserForm_QueryClose' event to cancel the scheduled Application.OnTime event by setting the Schedule parameter to False.
Manage Form Visibility with Workbook Events
Control the visibility of the UserForm based on which workbook is currently active using built-in workbook events.
Run and Edit VBA Macros Seamlessly in WPS Spreadsheet
WPS Spreadsheet provides robust support for VBA macros, allowing you to run, edit, and debug your UserForms and scripts with ease. Manage your VBA projects smoothly in a lightweight environment.
- 1. Open your macro workbook: Launch WPS Spreadsheet and open your macro-enabled workbook (.xlsm).
- 2. Access the Developer tools: Navigate to the 'Developer' tab on the top ribbon menu.
- 3. Launch the VBA Editor: Click 'Visual Basic' or press ALT + F11 to open the VBA Editor.
- 4. Edit and run your code: Implement the Application.OnTime fix in your modules and smoothly run your modeless UserForm.
Frequently Asked Questions
Why does DoEvents cause my modeless UserForm to close?
A continuous Do While loop with DoEvents consumes significant processing power and interrupts the internal event queue. When a separate workbook is closed, the application's event processor may abruptly terminate the modeless form's execution thread if it is caught in this continuous loop.
Where should I place my Application.OnTime timer code?
Timer procedures using Application.OnTime should be placed in a standard module rather than the UserForm module. This ensures the timer can continue running independently and can be cleanly cancelled even if the UserForm's state changes.
How do I stop an Application.OnTime schedule when the form closes?
In your UserForm's QueryClose event, call Application.OnTime using the exact scheduled time variable, and set the 'Schedule' parameter to False. This safely prevents the timer macro from triggering after the form has been dismissed.
Can I strictly link a UserForm to a specific workbook?
Yes, you can use the UserForm_Activate event to execute 'ThisWorkbook.Activate', which helps ensure the form remains tethered to the workbook that generated it. However, you must still manage your timers properly to prevent unexpected closures during workbook transitions.
