VBA Excel: UserForm mal fonctionnel après 6 secondes. Solved

Question

Hello,  I am using a non-modal form (fStatus) with a label (lblStatus) to display the progress of the macro exploration of a large Excel workbook (170 sheets of about 50 rows and 30 columns).  About 6 seconds after launching the macro, the form stops refreshing (on the screen) while the macro continues normally. At that point, the hourglass cursor is replaced by the normal cursor, and sometimes the form disappears and its location turns completely white.  I added a BlockInput to prevent any user interaction during the execution of the macro (before, right-clicking caused the same phenomenon).  Here is the code (very simplified):  Option Explicit Option Private Module Declare Function BlockInput Lib "user32" (ByVal fBlockIt As Long) As Long Public Sub Test() Dim frmStatus As fStatus Dim wsh As Excel.Worksheet Dim wbkCible As Excel.Workbook Dim cell As Excel.Range Dim ctr As Long Application.Calculation = xlCalculationManual Application.Cursor = xlWait Application.ScreenUpdating = False Set wbkCible = Application.ActiveWorkbook Set frmStatus = New fStatus frmStatus.Show BlockInput 1 For Each wsh In wbkCible.Worksheets wsh.Select frmStatus.lblStatus.Caption = wsh.Name frmStatus.Repaint For Each cell In wsh.UsedRange cell.Select ` For ctr = 1 To 2000: Next Next cell Next wsh Application.ScreenUpdating = True Application.Cursor = xlDefault Application.Calculation = xlCalculationAutomatic frmStatus.Hide Unload frmStatus Set frmStatus = Nothing BlockInput 0 End Sub  Different values for the loop ` For ctr = 1 To 2000: Next allowed me to verify that the problem occurs after a delay of about 6 seconds.  Does anyone have an idea of what is happening?   Configuration: XP / Mozilla / Office 2003  -- Nicolas always says: "It's easy when you know the answer!"

Mytå · Accepted Answer

Hello forum  There might be a solution with DoEvents.  DoEvents hands over control to the operating system until it has finished processing the events in its queue  Mytå -- Installed versions [MsProject 2003(FR), Excel 2003-2007(FR)]

gbinforme · Answer

Hello  This seems to be a well-known phenomenon for display in a long loop.  When you modify your title (lblStatus), try adding: lblStatus.Repaint This forces the display, which should work better. --  Always Zen

Patrice33740 · Answer

Hello gbinforme   The refresh of the form display is already present in the code: frmStatus.Repaint   When I try to apply it to the label (frmStatus.lblStatus.Repaint), I get a compilation error "Method or data member not found" since the method does not apply to labels.    --  Nicolas always says: "It's easy when you know the answer!"

gbinforme · Answer

Hello  Use a textbox to display instead, as a title is not meant for that.  It is indeed on the userform that the function should be applied, and if you don't want it to slow down the process too much, you shouldn't modify the content until after several seconds (20-30) because displaying takes much longer compared to the calculations. --  Stay calm

Patrice33740 · Answer

Hello gbinforme, thank you for your response.  I tried replacing the Label with a TextBox but it doesn't change anything!  However, I explored the option of adding multiple .Repaint in the loop even though the UserForm's content doesn't change.  By placing a .Repaint in the Cell loop, it works but, of course, the macro's duration becomes excessively long!  As a compromise, I tried using the Timer function to refresh every second, but it doesn’t work, the frequency is not sufficient.  I placed a counter in the Cell loop and conducted several tests with an increasing refresh frequency: it's not satisfactory.  Curiously, I found an acceptable compromise by performing a .Repaint at the beginning of each line (while with the previous method I needed several per line!):  For Each cell In wsh.UsedRange  If cell.Column = 1 Then frmStatus.Repaint ...  Next   This doesn’t solve the problem but simply prevents it from occurring.  I had already observed a similar phenomenon with Application.StatusBar when exploring large files: after a while, the display freezes, it is no longer updated with each change while the macro continues to run.  I think an external process outside of Excel takes control and doesn’t return it correctly.  There is probably a solution using the Windows API, but I haven’t found anything on this topic.  Patrice

Le Pingou · Answer

Hello, Just passing by, I used the following code to display a patience message (UserForm name) in a lengthy procedure for sending emails and it works well. The code:  Application.Cursor = xlWait ' displays the wait cursor Patience.Show vbModeless ' displays the UserForm but continues processing Patience.Repaint ' refreshes the displayed content otherwise we get a blank box ` **** ` Here is my processing procedure [ClientAdresseCourriel clofpro, clsage, nbli, clbcmde, connecte] `**** Workbooks(clsage).Close ` closes the address workbook to be processed Unload Patience ' close the UserForm Application.Cursor = xlDefault ' reset the cursor to default  -- Regards. The Penguin

Patrice33740 · Answer

Hello Le Pingou and gbinforme,  Thank you for taking an interest in my problem.  I declared my UserForm as non-modal using the VBE by setting ShowModal directly to False, but I noticed that ShowModal does not appear in the properties of the UserForm (that’s the only one missing), so doubt crept in, and I followed Le Pingou's advice:  I tried frmStatus.Show vbModeless to be more sure, but the problem persists.  A few clarifications: - In the loop causing the issue, the refresh rate is slightly over one second (it hangs on the 6th or 7th sheet). - I use this same UserForm in other procedures in the module and it works very well, but the refresh rate is higher (0.5 to 0.7 seconds). - In the loop, when I add a Repaint for each explored line (without changing the userform), the userform works correctly, but the execution time... - I am convinced that the size of the file being explored affects the application's display behavior, but that’s just a belief. - The explored file is not really huge: 9 MB containing mainly data, formulas, and a few Excel charts (less than fifteen), no external objects, no images, thousands of hyperlinks but all targeted at internal cells in the file.  Best regards Patrice   -- Nicolas always says: "It's easy when you know the answer!"

Patrice33740 · Answer

Hello gbinforme,I tried to refresh every 10, 5, 2 lines but it's not enough, it only works with 1 line (or less), plus the Repaints happen with each line even though the value doesn't change (it only changes for each sheet).At the beginning of the code I have a Application.ScreenUpdating = FalseThe amount of memory is more than sufficient to avoid swapping.--Nicolas always says: "It's easy when you know the answer!"

Patrice33740 · Answer

Hello again  If I don't put this Repaint, without any theoretical interest, the Userform goes on strike after 6 seconds!  That's where my problem lies.  Best regards Patrice   -- Nicolas always says: "It's easy when you know the answer!"

Patrice33740 · Answer

Thank you all,  Mytå's solution works well, I placed the DoEvents in the loop just before modifying the control:  For Each wsh In wbkCible.Worksheets DoEvents frmStatus.lblStatus.Caption = wsh.Name frmStatus.Repaint ....  The macro returns to its normal speed.  I mark the post as resolved  Patrice  -- Nicolas always says: “It’s easy when you know the answer!”

VBA Excel: UserForm mal fonctionnel après 6 secondes.

10 answers

Copy-paste from excel into google sheets

I don’t know, i don’t understand anything, i’m 76 years old and i’m not sure

No internet connection possible

Put in the correct order

Unable to open xsl and xslx files

How to create an imail

Self-invoking js function

Access to google internet tab blocked

Very slow computer

Unusable screen